Removing Emails - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Removing Emails

Author

Topic

djpin
Starting Member

27 Posts

Posted - 2014-09-11 : 10:00:24

Hi,

I do apologize - I miss posted this the first time...

I'm trying to remove emails from a table but the simple update query I wrote doesn't seem to do it. I figured I'd test one record instead of the entire dbase. I've probably given more info than needed (the select statement) but wanted to give a frame of reference.

SELECT
Name.ID,
EMAIL,
COMPANY,
MEMBER_TYPE
FROM
APSCU_PROD.dbo.Name
WHERE
EMAIL in
(SELECT EMAIL FROM Name WHERE EMAIL > '' GROUP BY EMAIL, left(ZIP,5) HAVING Count(*) > 1)
ORDER BY
Name.ID,
NAME.EMAIL--

Update APSCU_PROD.dbo.Name set
NAME.EMAIL=''
Where name.ID= '359'and
NAME.member_type= 'CE'

Thanks!

Thanks,

DJ

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-11 : 10:15:59

quote:
Originally posted by djpin

Hi,

I'm trying to remove duplicate emails but the simple update query I wrote doesn't seem to do it. I figured I'd test one record instead of the entire dbase. I've probably given more info than needed (the select statement) but wanted to give a frame of reference.

SELECT
Name.ID,
EMAIL,
COMPANY,
MEMBER_TYPE
FROM
APSCU_PROD.dbo.Name
WHERE
EMAIL in
(SELECT EMAIL FROM Name WHERE EMAIL > '' GROUP BY EMAIL, left(ZIP,5) HAVING Count(*) > 1)
ORDER BY
Name.ID,
NAME.EMAIL--

Update APSCU_PROD.dbo.Name set
NAME.EMAIL=''
Where name.ID= '359'and
NAME.member_type= 'CE'

Thanks!

Thanks,

DJ

To "REMOVE" don't you need a delete statement somewhere? I don't see any. Here is an example of how you can do it. But before you do it for real, test it in a test environment or test table.

;WITH cte AS
(      
	SELECT ROW_NUMBER() OVER (PARTITION BY email ORDER BY email) AS RN
	FROM  APSCU_PROD.dbo.NAME
	WHERE EMAIL > '' 
)DELETE cte WHERE RN > 1;

djpin
Starting Member

27 Posts

Posted - 2014-09-11 : 11:25:39

Hi James K,

I do apologize - I mislabeled my post this the first time...

I am trying to remove/delete the emails from a field. I figured I could achieve this by using an update statement.

The word "delete" scares me!!!!

Thanks,

DJ

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-11 : 11:31:28

The word delete scares me too :)

When I want to delete something, I wrap it in a transaction and roll back if anything goes wrong. For example the following will let you see how many rows would be deleted and undo the delete. You can also add other statements to examine if the deleted data was what you intended by inserting additional select statements before the rollback:

BEGIN TRAN
;WITH cte AS
(      
	SELECT ROW_NUMBER() OVER (PARTITION BY email ORDER BY email) AS RN
	FROM  APSCU_PROD.dbo.NAME
	WHERE EMAIL > '' 
)DELETE cte WHERE RN > 1;
ROLLBACK

If you are sure that what you deleted is what you want to delete, then you can change the rollback to commit and run again. But once you commit, it is committed! You can't roll it back.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-11 : 11:39:30

If you don't want to delete and just set the e-mail to an empty string, you can do it like shown below.

;WITH cte AS
(      
	SELECT ROW_NUMBER() OVER (PARTITION BY email ORDER BY email) AS RN, EMAIL
	FROM  APSCU_PROD.dbo.NAME
	WHERE EMAIL > '' 
)UPDATE cte SET EMAIL = '' WHERE RN > 1

djpin
Starting Member

27 Posts

Posted - 2014-09-12 : 09:40:37

I'm wasn't sure what I was doing wrong after running the query (thanks) it gave me the number of rows affected however, the data still existed in the field. But after sleeping on it I realize that the field I should be looking at is in a different table (Dah - to me!)

Which changes things (I think) Ok, let me give this another shot.

I want to remove the email addresses from the APSCU_PROD.dbo.Name_Address table where the (From the APSCU_PROD.dbo.Name) NAME.MEMBER_TYPE = 'CE', 'CO', 'COE'

Not sure how to do the join on the 2 tables?

So the fields needed are:
APSCU_PROD.dbo.Name_Address.email
APSCU_PROD.dbo.Name.Member_Type

Thanks.

Thanks,

DJ

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-12 : 09:58:57

There should be some column in the tables that will allow you to relate the two tables. A client_id or member_id perhaps?

djpin
Starting Member

27 Posts

Posted - 2014-09-12 : 10:03:39

Yes, APSCU_PROD.dbo.Name_Address.ID & APSCU_PROD.dbo.Name_Address.ID

Thanks,

DJ

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-12 : 10:09:15

quote:
Originally posted by djpin

Yes, APSCU_PROD.dbo.Name_Address.ID & APSCU_PROD.dbo.Name_Address.ID

Thanks,

DJ

Did you mean APSCU_PROD.dbo.Name_Address.ID & and APSCU_PROD.dbo.Name?

Assuming you did

;WITH cte AS
(      
	SELECT ROW_NUMBER() OVER (PARTITION BY email ORDER BY email) AS RN, a.EMAIL
	FROM  
		APSCU_PROD.dbo.NAME n
		INNER JOIN APSCU_PROD.dbo.Name_Address a ON
			a.ID = n.ID
	WHERE EMAIL > '' 
		AND n.MEMBER_TYPE IN ('CE','CO','COE')
)
-- select * from CTE order by EMail
UPDATE cte SET EMAIL = '' WHERE RN > 1

You can comment the UPDATE statement adn uncomment the select statement to see what the output from the CTE is. What you will replace is every row for which RN is greater than 1.

djpin
Starting Member

27 Posts

Posted - 2014-09-12 : 10:44:28

James K - Outstanding sir!!!! Thank You. - (still not fully understanding but I'll get there :-) )

I had to tweak it a little but it works!

;WITH cte AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY a.email ORDER BY a.email) AS RN, a.EMAIL, a.COMPANY, a.ID
FROM
APSCU_PROD.dbo.Name_Address a
INNER JOIN APSCU_PROD.dbo.Name n ON
a.ID = n.ID
WHERE a.EMAIL > ''
--and a.ID= 326
AND n.MEMBER_TYPE IN ('CE','CO','COE')
)select * from CTE order by Email
--UPDATE cte SET EMAIL = '' --WHERE RN > 1

Thanks,

DJ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources