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

Question

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.

Narbonne

Is this now possible with SQL Server 2012 or 2014? I have a horribly constructed set of tables for my class project which has ON DELETE RESTRICT constraints on both tables at once. I don't want to ask another question as it'd be a dupe of this one. – Petrinapetrine

I think this is still not supported in SQL Server – Argol

Answer 1

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.

Reduce

That must be why I'm getting an 'incorrect syntax error on the comma separating the tables 'DELETE table1, table2' Incorrect syntax near ','. – Homoousian

It works in MariaDB. DELETE t1, t2 FROM t1 JOIN t2 ON t2.id = t1.t2_id WHERE t1.id = '5' – Halogen

Answer 2

Why you don't use a DELETE CASCADE FK ?

Demasculinize

Answer 3

This cannot be done in one statement. You will have to use 2 statements

DELETE FROM TB1 WHERE PersonID = '2';
DELETE FROM TB2 WHERE PersonID = '2';

Deterrent

Answer 4

As i know, you can't do it in a sentence.

But you can build an stored procedure that do the deletes you want in whatever table in a transaction, what is almost the same.

Aryl

Answer 5

I don't think you can delete from multiple tables at once (though I'm not certain).

It sounds to me, however, that you would be best to achieve this effect with a relationship that cascades deletes. If you did this you would be able to delete the record from one table and the records in the other would be automatically deleted.

As an example, say the two tables represent a customer, and the customer's orders. If you setup the relationship to cascade deletes, you could simply delete record in the customer table, and the orders would get deleted automatically.

See the MSDN doc on cascading referential integrity constraints.

Cop

Answer 6

Specify foreign key for the details tables which references to the primary key of master and set Delete rule = Cascade .

Now when u delete a record from the master table all other details table record based on the deleting rows primary key value, will be deleted automatically.

So in that case a single delete query of master table can delete master tables data as well as child tables data.

Contiguous

Answer 7

Use this in procedure

declare cu cursor for SELECT [name] FROM sys.Tables where [name] like 'tbl_%'
declare @table varchar(100)
declare @sql nvarchar(1000)

OPEN cu  
FETCH NEXT FROM cu INTO @table 

WHILE @@FETCH_STATUS = 0  
BEGIN  
    set @sql = N'delete from ' + @table
    EXEC sp_executesql @sql
    FETCH NEXT FROM cu INTO @table 
END   
CLOSE cu;  
DEALLOCATE cu;

Cay

Answer 8

I'm not sure why folks make this so difficult. It is possible to DELETE rows from multiple tables. Here is my query statement that works.

$sql = "DELETE clients, equine_notes, client_horses FROM clients INNER JOIN client_horses ON client_horses.cID = clients.id INNER JOIN equine_notes ON equine_notes.cID = clients.id WHERE clients.id = '".$cID."'";

The clients table has the client's name. The client_horses are multiple rows of how many horses the client owns and equine_notes are notes that I have made on specific horses. I can delete everything pertaining to this client with the one query statement using the INNER JOIN clause.

Hope this helps

Bayonne

Have you actually used this, or is this a case of "I assume this works"? At least for me, in SQL Server 2019 Microsoft SQL Server 2019 (v. 15.0.4345.5), I get a syntax error on the comma separating the tables to delete from. This happens whether I use the direct table names or aliases. This is my query: DELETE ActionAttachment, Action FROM ActionAttachment INNER JOIN Action ON Action.ActionID = ActionAttachment.ActionID WHERE ActionAttachment.ActionID in (5, 6, 8) – Sophomore

Answer 9

I use this for cleaning up data in test/development databases. You can filter by table name and record count.

DECLARE @sqlCommand VARCHAR(3000);
DECLARE @tableList TABLE(Value NVARCHAR(128));
DECLARE @TableName VARCHAR(128);
DECLARE @RecordCount INT;

-- get a cursor with a list of table names and their record counts
DECLARE MyCursor CURSOR FAST_FORWARD
FOR SELECT t.name TableName,
           i.rows Records
    FROM sysobjects t,
         sysindexes i
    WHERE 
          t.xtype = 'U'              -- only User tables
          AND i.id = t.id          
          AND i.indid IN(0, 1)       -- 0=Heap, 1=Clustered Index
          AND i.rows < 10            -- Filter by number of records in the table
          AND t.name LIKE 'Test_%';  -- Filter tables by name. You could also provide a list:
                                     -- AND t.name IN ('MyTable1', 'MyTable2', 'MyTable3');
                                     -- or a list of tables to exclude:
                                     -- AND t.name NOT IN ('MySpecialTable', ... );

OPEN MyCursor;

FETCH NEXT FROM MyCursor INTO @TableName, @RecordCount;

-- for each table name in the cursor, delete all records from that table:
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sqlCommand = 'DELETE FROM ' + @TableName;
        EXEC (@sqlCommand);
        FETCH NEXT FROM MyCursor INTO @TableName, @RecordCount;
    END;

CLOSE MyCursor;
DEALLOCATE MyCursor;

Reference info:

• sysobjects
• sysindexes
• SQL Server Cursors

Neurocoele

Answer 10

Generally I do deletions from multiple tables with one query. It works correct with PostgreSQL, but doesn't work for MSSQL, and that's why I got here.
With Postgres (I don't know about other DBs) you can do:

WITH obsolete_ids AS (
    SELECT pr_r."ID" AS ids
    FROM "PULL_REQUEST" pr_r
    WHERE 1=1
    AND pr_r."STATUS" IN (1)
) , del_commit_junc AS (
DELETE 
FROM "PR_TO_COMMIT"
WHERE "REQUEST_ID" IN (SELECT ids FROM obsolete_ids)
)
DELETE
FROM "PULL_REQUEST" pr_out
WHERE pr_out ."ID" IN (SELECT ids FROM obsolete_ids)

Actually In my original Query I delete foreign keys from 2 more tables, but here I just paste an example. That way I solved problem with Foreign Keys in "PR_TO_COMMIT" table.

Shinar

Answer 11

The way I am using to Delete rows from multiple tables in SQL Server? The most important is to use on delete cascade when creating a foreign key in the table

#Table 1 Create:# 
    
create table Customer_tbl 
(
C_id int primary key, 
C_Name varchar(50), 
C_Address varchar(max), 
City varchar(50)
);


#Table 2: Create with Foreign Key Constraints#

create table [order] 
(
Ord_Id int primary key, 
Item varchar(50), 
Quantity int, 
Price_Of_1 int, 
C_id int foreign key references Customer_tbl(C_id)
on delete cascade

);

delete from Customer_tbl where C_id = 2;

Maryrosemarys

Answer 12

CREATE PROCEDURE sp_deleteUserDetails
    @Email varchar(255)
AS
    declare @tempRegId as int
    Delete UserRegistration where Email=@Email  
    set @tempRegId = (select Id from UserRegistration where Email = @Email)
    Delete UserProfile where RegID=@tempRegId

RETURN 0

Separable

Answer 13

You can use something like the following:

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name IN ("TB2","TB1")  -- use these databases

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   


WHILE @@FETCH_STATUS = 0   
BEGIN   

       DELETE FROM @name WHERE PersonID ='2'

       FETCH NEXT FROM db_cursor INTO @name   
END  

Raw

That won't work. You need to use something like exec('DELETE ' + @tableName) – Inquiring

Answer 14

Try this query

DELETE TB1, TB2 FROM TB1 INNER JOIN TB2  
WHERE TB1.PersonID = TB2.PersonID and TB1.PersonID = '2'

Thomsen

Answer 15

$qry = "DELETE lg., l. FROM lessons_game lg RIGHT JOIN lessons l ON lg.lesson_id = l.id WHERE l.id = ?";

lessons is Main table and lessons_game is subtable so Right Join

Lorrettalorri

Answer 16

DELETE TB1, TB2
    FROM customer_details
        LEFT JOIN customer_booking on TB1.cust_id = TB2.fk_cust_id
    WHERE TB1.cust_id = $id

Portland

A plain-English explanation wouldn't hurt. See stackoverflow.com/help/how-to-answer – Powwow