SQL DELETE STATEMENT explanation

Question

This request works:

There are two successive FROMs. When I delete the first one, it doesn't work why?

DELETE FROM TableA FROM TableA dim LEFT OUTER JOIN ( SELECT DISTINCT ColA FROM TableB UNION ALL SELECT DISTINCT ColA FROM tableC ) A ON A.ColA= dim.ColA WHERE A.ColA IS NULL

EDIT This one doesn't work which I was supposing was the correct one:

 DELETE FROM TableA dim LEFT OUTER JOIN ( SELECT DISTINCT ColA FROM TableB UNION ALL SELECT DISTINCT ColA FROM tableC ) A ON A.ColA= dim.ColA WHERE A.ColA IS NULL

Thanks

Can you share the statement that doesn't work and the error you're getting? — Mureinik
– Mureinik, Commented Mar 20, 2019 at 16:04
Syntax for multi-table DELETE statements varies across RDBMS (MySQL, Oracle, Microsoft SQL Server, et al.). "doesn't work" is close to useless in describing the actual behavior that is observed. Is it an error message? Are no rows deleted? Are too many rows deleted? What does "doesn't work" mean? — spencer7593
– spencer7593, Commented Mar 20, 2019 at 16:06
Does it really work to have ColA first in an equal condition ON A.ColA = and then in a NULL comparison WHERE A.ColA IS NULL? — Joakim Danielson
– Joakim Danielson, Commented Mar 20, 2019 at 16:06
@JoakimDanielson: that's the pattern for an anti-join. Notice that it's an outer join to the inline view A... the condition in the WHERE clause will eliminate rows that had a match, leaving rows from dim that don't have a matching row. (As to whether that works in a DELETE statement, that really depends on the RDBMS, what syntax is accepted and supported.) — spencer7593
– spencer7593, Commented Mar 20, 2019 at 16:08

Eric Brandt · Accepted Answer · 2019-03-20 16:13:18Z

It's because of the table join. The SQL Engine needs to know which source to delete from, and the odd FROM FROM syntax accomplishes that.

The statement you have is functionally equivalent to this, which is exactly the same except I substituted the table alias for the first FROM clause:

DELETE dim FROM TableA dim LEFT OUTER JOIN ( SELECT DISTINCT ColA FROM TableB UNION ALL SELECT DISTINCT ColA FROM tableC ) A ON A.ColA= dim.ColA WHERE A.ColA IS NULL

Yogesh Sharma · Accepted Answer · 2019-03-20 16:14:09Z

You can use NOT EXISTS :

DELETE dim FROM TableA dim WHERE NOT EXISTS (SELECT 1 FROM TableB B WHERE B.ColA = DIM.ColA) OR NOT EXISTS (SELECT 1 FROM TableC C WHERE C.ColA = DIM.ColA);

Your delete statement has two from clauses which is not correct, it should have only one immediate after delete statement:

DELETE dim FROM TableA dim LEFT OUTER JOIN (SELECT ColA FROM TableB UNION SELECT ColA FROM tableC ) A ON A.ColA= dim.ColA WHERE A.ColA IS NULL;

Also, DISTINCT with UNION ALL is redundant here if you want UNIQUE entry then use UNION instead.

There is nothing incorrect about two FROM - that is allowed in the grammar

Collectives™ on Stack Overflow

SQL DELETE STATEMENT explanation

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related