?
Solved

SQL Server delete statement

Posted on 2007-07-28
2
Medium Priority
?
218 Views
Last Modified: 2010-03-19
I have a database I inhetited

There is a UID in a table that gets copied into other tables on certain transactions.

I would like to know how I can delete from multiple tables where pmrID = # with one SQL statement
0
Comment
Question by:lrbrister
2 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 19584640
try this

2 tables
----------
delete table1, table2 from table1, table2 where table1.pmrID = 123 and (table1.pmrID = table2.pmrID)

3 tables
----------
delete table1, table2, table3 from table1, table2, table3 where table1.pmrID = 123 and (table1.pmrID = table2.pmrID or table1.pmrID = table3.pmrID)

And so on...
0
 
LVL 3

Accepted Solution

by:
Askeeto earned 2000 total points
ID: 19584664
You can not delete from multiple tables in one sql statement. But you can specify the "on delete cascade" option on foreign keys.

ALTER TABLE YourTable
ADD CONSTRAINT fk_YourTable
FOREIGN KEY (YourField) REFERENCES YourOtherTable(YourField)
ON DELETE CASCADE
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question