Delete records in different tables

Question

I need to delete the records where UserIndex = 1 and ItemNumber = 5202 in all three tables, all in a single query. I am using SQL 2008 R2.

Table UserInfo1

UserIndex	Itemnumber	ItemCount
1	5202	99
1	1600	50
2	155	2
3	125	60

Table UserInfo2

UserIndex	Itemnumber	ItemCount
8	1265	50
4	1899	41
1	5202	99
3	125	60

Table UserInfo3

UserIndex	Itemnumber	ItemCount
6	5205	85
1	6666	41
3	4455	44
1	5202	50

I'm trying to use this query with two tables, but it doesn't work:

DELETE ItemInfo1, ItemInfo2 
FROM ItemInfo1 
LEFT JOIN ItemInfo2 
    ON ItemInfo1.UserIndex = ItemInfo2.UserIndex 
WHERE ItemInfo1.UserIndex = 1;

Answer 1

You can't ordinarily delete from more than one table in a single T-SQL DELETE statement.

It is possible to achieve the same effect using triggers or cascading foreign keys with an ON DELETE CASCADE option, but that's probably not what you need.

If you just want to submit a single batch of text and ensure all three deletion actions either succeed or fail as a unit, use a transaction:

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

        DELETE FROM dbo.UserInfo1
        WHERE
            UserIndex = 1 
            AND ItemNumber = 5202;

        DELETE FROM dbo.UserInfo2
        WHERE
            UserIndex = 1 
            AND ItemNumber = 5202;

        DELETE FROM dbo.UserInfo3
        WHERE
            UserIndex = 1 
            AND ItemNumber = 5202;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    DECLARE @msg nvarchar(2048) = ERROR_MESSAGE();
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    RAISERROR (@msg, 16, 1);
    RETURN;
END CATCH;

More generally, it is a bad pattern to have such similar tables distinguished only by a suffix number. This suggests you really ought to have a single table instead with an extra column to distinguish the three sets.

A single statement DELETE would then be trivial.