delete rows from multiple tables

[lothadio]

Is there a way to delete from multiple tables with one SQL statement?

example

DELETE
FROM temp_approval
WHERE document_number = "$document_number'

DELETE
FROM temp_final
WHERE document_number = "$document_number'

DELETE
FROM temp_reasons
WHERE document_number = "$document_number'

DELETE
FROM temp_comments
WHERE document_number = "$document_number'

What I need to do is consolidate this into 1 SQL statement?

Can you do this?

ALso the same question with

INSERT
INTO
VALUES

 

[tlbroadbent]

There is no way to delete or insert rows in multiple tables using one statement as you have requested. There are some other ways to do this

You can submit all of the delete statements as one batch to SQL Server and it will handle the deletes sequentially. Enclose the delete statements in a transaction.

Begin Transaction

DELETE
FROM temp_approval
WHERE document_number = "$document_number'

DELETE
FROM temp_final
WHERE document_number = "$document_number'
.
.
.
Commit Transaction

Doing it this way will make sure all of the Deletes (or Inserts) occur or none will occur. It accomplishes what you want.

Another way to do this is to create a trigger on one of the tables that will delete corresponding data in the remaining tables upon deletion of a row in that table.

Inserts can be in triggers if all the data for the rows is known upon insertion of a row in the master table. However, this is usually not the case. Therefore, executing multiple Inserts in one batch is likely a better solution.
Terry

http://members.home.net/tlbroadbent/index.htm

 

[Cagliostro]

If you have a big code, difficult to edit, use stored procedures. John Fill

ivfmd@mail.md

 

[tlbroadbent]

John Fill makes a good point. You could put all of the deletes or inserts in a stored procedure. You then need to execute only one SQL statement.

exec MyDeleteProc Terry

http://members.home.net/tlbroadbent/index.htm