SQL Query to delete from two tables

N

n64man120

2[H]4U
Joined
Jan 11, 2004
Messages
3,498
I'm trying to form a single query to do the following:

delete FROM table1 where table1.field1 < table1.field2

delete FROM table2 where table2.field3 = table1.field3 **Only use table1 rows selected above to be removed**

So I'm using logic in the first query to determine which rows I want to remove, and I need to use that logic to decide which table2 rows to remove as well, if they match field3 from the table1 row.

Anyone know how to join this into one string? I can't seem to figure it out.

Thanks
 
You need two queries.

The first:

DELETE FROM table2 WHERE field3 IN (SELECT field3 FROM table1 WHERE field1 < field2)

The second:

DELETE FROM table1 WHERE field1 < field2
 
You could build an array of successfully deleted items when query 1 executes, then loop through that array to delete the items in query 2... that way you ensure that only items successfully delete in query 1 are processed in query 2.

Obviously not the most efficient way of doing it, but if you wanted to ensure you don't delete something from the second table that wasn't removed from the first for whatever reason, it'd protect you there.

Edit: Obviously my method extends Arainach's... in that you'd need 2 queries to process two separate tables with a delete. I've never tried to UNION a delete query, but I don't think it would work...
 
You could declare a temp table in the transaction that would hold the needed cross-reference values from the first DELETE statement, then execute your first DELETE as normal, and finally have your second DELETE statement be based off the values stored in the temp table. When everything's done, kill the temp table from memory.

This idea can obviously be expanded out be more granular for per-record deletion logic, such as having a parent transaction call some code to delete a row from the first table, check its success/failure, and only proceed with the second table's row deletion(s) if business rules passed. It all depends on how granular you want to make this.
 
You don't mention which dialect of SQL you're using. If you're using Microsoft SQL Server, you can use the OUTPUT clause in the DELETE statement to develop a table variable, then use that table variable to key the second DELETE statement.
 
I would go the temp table route. I have done this in the past with good results. Just delete your temp tables.
 
A table variable is just as fast--usually faster--and doesn't require any deletion management.
 
You must log in or register to reply here.
Back
Top