delete records from am table with information from a second table

Question

Post reply

delete records from am table with information from a second table

Peter Priebusch

SSC Rookie

Points: 30
More actions
January 22, 2010 at 6:36 am

#209361

Hello everybody,
I've been searching a while, but did not find a way how to do this.
I have a table (1) with about 900 records like
Customer-NR, Info, Email
and another table (2) with about 5000 records like
Email
Now I like to delete all records in table (1) where the email address is found in table (2).
Does anybody have an idea how to handle this?

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

delete from table1

where email not in (Select email from table2)

This will delete all records that does not have any corresponding email address in the second table.

-Roy

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

Check this pseudo-code...

delete table1

where table1.email in (select email from table2 where table2.email = table1.email)

go

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

Roy Ernest (1/22/2010)
delete from table1
where email not in (Select email from table2)
This will delete all records that does not have any corresponding email address in the second table.

:w00t: nahhhh! it's the other way around 😀

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

ooops.... Thanks for the correction Paul. I thought the OP wanted to delete all records from table1 when there is no corresponding email in table2.

Great catch...

-Roy

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

Roy Ernest (1/22/2010)
Great catch...

Glad to help 🙂

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Peter Priebusch SSC Rookie Points: 30 More actions · Answer 6

Peter Priebusch

SSC Rookie

Points: 30

January 22, 2010 at 7:07 am

#1107316

Wow, perfect.

Many thanks.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 7

Peter Priebusch (1/22/2010)
Wow, perfect.
Many thanks.

"I love it when a plan comes togheter" 😎

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.