Delete based on list from another table

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

SQL Server 2005 Forums

Transact-SQL (2005)

Delete based on list from another table

Author

Topic

pvong
Yak Posting Veteran

58 Posts

Posted - 2010-10-26 : 16:20:48

I'm a newbie, please help.

Table 1 has
FirstName
LastName
Emails

Table2 has
Emails

Is it possible to write a simple Delete Statement that will delete all rows where the email exist from Table2? I have 100s of emails in table2 and 1,000s of emails in table1 and need to delete those 100s from table1.

Thanks!

------------------------------
Using VS2008 / Learning in VB.Net 2.0 / Win2003 / SQL 2005 w SP2
Be kind to the newbies because you were once there.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-26 : 17:44:42

welcome to sql forum!

first sentence says to delete row, second sentence says to delete emails, which one do you want to do? delete emails or delete rows?

If you don't have the passion to help people, you have no passion

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-26 : 23:10:43

Hi,

What i understand is that you want to delete from Table1 all the records where email exists in table2.

If my understanding is correct then you can try the following statement.

Delete from table1 where email in (select email from table2)

If my understanding is wrong then please provide some sample data and expected output

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

pvong
Yak Posting Veteran

58 Posts

Posted - 2010-10-27 : 09:51:12

Sorry if I was not clear. I want the whole row to be gone. Here is my Delete command but it returned 0 rows. I double and triple checked to make sure the emails exist on both tables and it does.

DELETE FROM MktingEmailList
WHERE (Email = 'select * from mktingdonotsendemails')

Thanks again from a newbie.

------------------------------
Using VS2008 / Learning in VB.Net 2.0 / Win2003 / SQL 2005 w SP2
Be kind to the newbies because you were once there.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-27 : 10:16:05

Try:
DELETE FROM MktingEmailList
WHERE Email in (select email from mktingdonotsendemails)

I am assuming that email column is existing in mktingdonotsendemails. If not the change the email value in
(select email from mktingdonotsendemails) to
(select yourcolumnname from mktingdonotsendemails)

pvong
Yak Posting Veteran

58 Posts

Posted - 2010-10-27 : 10:46:28

The only Column in the mktingdonotsendemails table is Email. When I tried your command,

DELETE FROM MktingEmailList
WHERE Email in (select email from mktingdonotsendemails)

it deleted ALL 25000 records from my mktingemaillist. I have plenty of backups so it was no problem to restore. I just want to delete the 100+ rows that are on the do not email table.

------------------------------
Using VS2008 / Learning in VB.Net 2.0 / Win2003 / SQL 2005 w SP2
Be kind to the newbies because you were once there.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-27 : 12:39:49

Can you please execute the below select and let me know the result

select count(*) from MktingEmailList
WHERE Email not in (select email from mktingdonotsendemails)

pvong
Yak Posting Veteran

58 Posts

Posted - 2010-10-29 : 15:28:14

Actually, this command worked. I really appreciate it!!!!

DELETE FROM MktingEmailList
WHERE Email in (select email from mktingdonotsendemails)

------------------------------
Using VS2008 / Learning in VB.Net 2.0 / Win2003 / SQL 2005 w SP2
Be kind to the newbies because you were once there.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-30 : 03:26:59

quote:
Originally posted by pvong

Actually, this command worked. I really appreciate it!!!!

DELETE FROM MktingEmailList
WHERE Email in (select email from mktingdonotsendemails)

------------------------------
Using VS2008 / Learning in VB.Net 2.0 / Win2003 / SQL 2005 w SP2
Be kind to the newbies because you were once there.

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources