Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Delete entire row with inner join

Status
Not open for further replies.

micang

Technical User
Joined
Aug 9, 2006
Messages
626
Location
US
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:

Code:
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


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

Code:
[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
 
Thanks George, much appreciated.

Michael
 
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
 
Just to add a check to see what you are going to delete before you do it
Code:
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
 
Hi all

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

Michael
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top