Delete entire row with inner join

[micang]

SQL 2000

Hi All,

I have Table1 and Table2. Table2 has a list of unique id's that I would like to delete the whole row from table1 with the same unique id.

I have been doing it like so:

delete *
from table1
where unique_id in (6,8,9)

But like this I have to type in the id's. It's OK if there are a few.

So I would like to know how to delete all rows from Table1 where Table2 and table1 unique id is equal. (an inner join)

Thanks in advance.

Michael

 

[gmmastros]

As always with delete statements, make sure you have a good backup before running this.

[COLOR=blue]Delete[/color] Table1
[COLOR=blue]from[/color]   table1
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] Table2
         [COLOR=blue]On[/color] Table1.Id = Table2.Id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[micang]

Thanks George, much appreciated.

Michael

 

[MichaelintheUK]

Hello Michael

You would think something like this was straightforward but joins etc will not work. It seems to need a nested query.
You could try something like this

I have 2 tables table1 and table2 and I want to delete all the records in table1 that have a maitch in table2

Query Design

Add: Table1


In Query Design Column 1

Field id
Table table1
Delete WHERE
Criteria IN (SELECT id FROM table 2)

Or in SQL

DELETE Table1.id
FROM Table1
WHERE Table1.id IN(select id from table2);

hope this helps

thanks

Michael

 

[SQLSister]

Just to add a check to see what you are going to delete before you do it

Delete Table1
--select * 
from   table1
       Inner Join Table2
         On Table1.Id = Table2.Id

Run the select statement first to make sure that your delete results are what you were expecting, then you can run the delete.
The more complicated your joins are the better it is to do this. So much easier to not delete the worng thing than to recover from deleting the worng thing.

And if we are deleting lots of records from a very mission critical table, we usually take that select and insert those records into a backup table making it easier to recover if something goes amiss.

Questions about posting. See faq183-874

 

[micang]

Hi all

Thank you so much for all the input, things much clearer now.

Michael