Delete rows from multiple tables using a single query (SQL Express 2005) with a WHERE condition

sql sql-server sql-server-express sql-server-2005-express
Jobi picture Jobi · Nov 11, 2009 · Viewed 131.4k times · Source

This is the query I'm using:

 DELETE TB1.*, TB2.*
   FROM TB1
        INNER JOIN TB2 ON TB1.PersonID = TB2.PersonID 
  WHERE (TB1.PersonID)='2'

It's working fine in MS Access but getting error (Incorrect syntax near ','.) in SQL Server Express 2005.

How to solve it? Please help.

Answer

Matijs picture Matijs · Nov 11, 2009

You cannot DELETE from multiple tables with a single expression in SQL 2005 - or any other standard SQL for that matter. Access is the exception here.

The best method to get this effect is to specify FOREIGN KEYS between the table with an ON DELETE trigger.

Related questions

SQL Server after update trigger

I have a problem with this trigger. I would like it to update the requested information only to the row in question (the one I just updated) and not the entire table. CREATE TRIGGER [dbo].[after_update] ON [dbo].[MYTABLE] …

Read More
How can I do a BEFORE UPDATED trigger with sql server?

I'm using Sqlserver express and I can't do before updated trigger. There's a other way to do that?

Read More
Connecting to SQL Server Express - What is my server name?

I was just given a laptop to perform some development from a client and I am currently in the process of setting it up. Visual Studio 2010 is installed as well as SQL Server Management Studio 2008 R2. I'm trying to open …

Read More