Delete rows from multiple tables using a single qu

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.

Answer 1

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  

Answer 2

you can join like this

DELETE t2
   FROM TB1 t1
        INNER JOIN TB2 t2 ON t1.PersonID = t2.PersonID 
  WHERE t1.PersonID = '2'

but as Alex mentioned, only one at a time.

You need cascade constraint on the table to do all at once

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';

Answer 4

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

Answer 5

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.