Deleting records in multiple SQL tables at once (with outer join!)

Question

I have two tables (t1 and t2), and t2 has zero or more rows that have a FK to t1's primary key. Note the zero rows which requires an OUTER JOIN.

I wish to delete the record in t1 corresponding to a given t1 primary key, and any related rows in t2.

I don't wish to use cascading deletes.

I've messed around with the following, but am not sure it is working.

Is this valid SQL?

Thanks

DELETE t1,t2
FROM t1
LEFT OUTER JOIN t2 ON t2.t1_id=t1.id
WHERE t1.id=123;

@GeniaS. Thanks. I saw these, but they seem to pertain to inner joins. If an outer join doesn't match, is the first table's record still deleted? — user1032531, Jun 08 '14 at 02:55
@GeniaS. Me neither, thus the question. I know how to deal with inner joins. Hopefully, my question will not be closed without an answer. — user1032531, Jun 08 '14 at 03:12
AFAIK you cant do it with single query, u dont want to cascade delete nor seperate queries. i'll search for this — marathonman, Jun 08 '14 at 03:33
@marathonman I've implemented it, and it seems to work perfect. Before going to production, would be good to know for sure! — user1032531, Jun 08 '14 at 03:36
This question asks about deleting multiple tables with an `outer join`, the other question is about a single table with an `inner join`. Hard to see how this question duplicates the older one. — Andomar, Jun 08 '14 at 05:40

score 1 · Answer 1 · answered Jun 08 '14 at 05:32

1

The short answer - yes, this is valid SQL. The same considerations when deleting using an inner join apply to outer joins too.

answered Jun 08 '14 at 05:32

Mureinik

1 Answers1