How to delete rows from 3 tables based on a specific value in one table

Question

Helo Folks,

I have 3 tables. A,B,C.

I have in Table A rows whose column'date' has value '2000-1-1'.

I like to delete rows from 3 tables based on where A.date = '2000-1-1'.

Considering a join on A.ID = B.ID.. or some other kind of join between tables.

You can't delete from three tables at the same time. You need three separate statements. — Patrick Hofman, Jul 31 '14 at 19:31
A DML statement (insert, delete, etc) can only affect one table. — Andrew, Jul 31 '14 at 19:32
The question is about "how to do it", they don't require one statement. This can be done with 3 statements, inside a transaction. — ypercubeᵀᴹ, Jul 31 '14 at 19:33
We're going to need more information if you want an answer to your question. Are you looking for multiple statements, a stored procedure, a set of triggers? — Dan Bechard, Jul 31 '14 at 19:43

M.Ali · Answer 1 · 2014-08-01T12:23:24.543

1

You cannot issue a single delete statement against 3 table but you can wrap 3 delete statements against 3 different tables in One Transaction.

BEGIN TRANSACTION

    DELETE FROM TABLE_A
    WHERE EXISTS (SELECT 1
                  FROM Table_X X
                  WHERE TABLE_A.ID = X.ID)

    DELETE FROM TABLE_B
    WHERE EXISTS (SELECT 1
                  FROM Table_X X
                  WHERE TABLE_B.ID = X.ID)

    DELETE FROM TABLE_C
    WHERE EXISTS (SELECT 1
                  FROM Table_X X
                  WHERE TABLE_C.ID = X.ID)

COMMIT TRANSACTION;

edited Aug 01 '14 at 12:23

answered Jul 31 '14 at 19:48

M.Ali

67,945
13
101
127

1

what is table X? fourth table? – user3054077 Jul 31 '14 at 20:13

How to delete rows from 3 tables based on a specific value in one table

1 Answers1