1

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

1 Answer 1

5

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.