January 3, 2012 at 10:44 pm
Hi there,
I have a table that is used to service a email distribution Listsystem, I need to remove dupicate email fields from different email lists.
Essentially I need a query that finds equivalent fields 'email' within the one table but removes the equivalnet 'email field where the qualifying field, eg 'Listname', so the email only goes to one particular list not two. To do this manually would be impossible consider we have over 50,000 emails!
Any tips would be great, thanks.
January 3, 2012 at 10:47 pm
Can you post some sample data?
Shatrughna
January 3, 2012 at 10:51 pm
Try this article on how to do it using a CTE.
http://jasonbrimhall.info/2011/03/21/dedupe-data-cte/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 3, 2012 at 11:01 pm
shatrughna (1/3/2012)
Can you post some sample data?
And it actually has more than 10 rows of test data. Nicely done, Jason.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 3, 2012 at 11:13 pm
Jeff Moden (1/3/2012)
And it actually has more than 10 rows of test data. Nicely done, Jason.
Thanks Jeff
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 4, 2012 at 4:03 pm
OK here is some sample data:
Email Name List Name Member ID
f.leffer@gmail.comFiona Leffer enews 280009
f.leffer@gmail.comNULL kidsclub 363184
f.leffer@gmail.comNULL members 358305
x.bridge@ccgs.edu.auTony Bridge education 182675
The issue is the email address that is a member of 'enews' should not get 'kidsclub' and 'members' emails so those
two need to be removed.
thanks!
January 4, 2012 at 4:21 pm
Can a subscriber have a subscription to one of those two and not subscribe to enews?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 4, 2012 at 5:10 pm
No a members that is subscribe to members should not be subscribed to e-news as they both get the same emails.
I know it's a stupid business, but that's how they roll!
January 4, 2012 at 6:06 pm
I'm guessing there are some normalization issues based on what you've provided. If you would like additional help, it would be helpful to us if you provide more specific DDL with some ready-to-use inserts of sample data.
create table dbo.TestTable
( RecNo int identity(1,1) primary key
, Email varchar(256)
, name varchar(200)
, ListName varchar(100)
, MemberID int );
insert into dbo.TestTable values
('f.leffer@gmail.com', 'Fiona Leffer', 'enews', 280009),
('f.leffer@gmail.com', NULL, 'kidsclub', 363184),
('f.leffer@gmail.com', NULL, 'members', 358305),
('x.bridge@ccgs.edu.au', 'Tony Bridge', 'education', 182675);
Now, to delete the 'kidsclub' and 'members' entries for the emails that are also associated with 'enews', you can do something like the following:
with cteEnews as
(
select distinct Email
from dbo.TestTable
where ListName = 'enews'
)
delete t
from cteEnews as cte
join dbo.TestTable as t
on t.Email = cte.Email
where t.ListName in ('kidsclub', 'members');
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply