Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is `Delete From Join` Standard SQL?

Among other questions, this one asked how to delete from a join.

My question: How much of this is standard SQL? On which databases would this actually work (most notably for me would be Oracle, MySQL and SQLServer)?

like image 327
Thorsten Avatar asked Oct 19 '09 22:10

Thorsten


People also ask

Can we use delete with join in SQL?

A DELETE statement can include JOIN operations. It can contain zero, one, or multiple JOIN operations. The DELETE removes records that satisfy the JOIN conditions.

Can you delete with an inner join?

Delete with inner join is used to delete all the records from the first table and all the matching records from the second table.

Which SQL clause is used to delete?

The DELETE Statement in SQL is used to delete existing records from a table. We can delete a single record or multiple records depending on the condition we specify in the WHERE clause.

How do you use inner join and delete in SQL?

Just add the name of the table between DELETE and FROM from where you want to delete records, because we have to specify the table to delete. Also remove the ORDER BY clause because there is nothing to order while deleting records. This one works on SQL Server if you only intend to delete from the first table.


3 Answers

Not standard according to International Standard ISO/IEC 9075:1992 Section 13.6, pages 384-386. Delete with JOIN syntax won't work in Oracle.

like image 57
DCookie Avatar answered Oct 17 '22 16:10

DCookie


DELETE ... FROM .. Is not part of the ANSI standards, nor UPDATE ... FROM ... for that matter. This includes any join syntax, since the join can only be specified with a FROM.

All vendors implement this though in one form or another.

like image 6
Remus Rusanu Avatar answered Oct 17 '22 15:10

Remus Rusanu


this will actually work in Oracle. The FROM keyword is optional: DELETE (query) is the same as DELETE FROM (query).

The rules to DELETE a JOIN are the same as the rules to update a JOIN: Oracle will modify the rows from only one table and only if there is no ambiguity in identifying the rows of the base table. Look in this SO or this SO for example.

Let's build a small example:

CREATE TABLE t_parent (ID NUMBER PRIMARY KEY, NAME VARCHAR2(10));
CREATE TABLE t_child (ID NUMBER PRIMARY KEY, 
                      parent_id NUMBER REFERENCES t_parent);
INSERT INTO t_parent VALUES (1, 'a');
INSERT INTO t_child VALUES (1, 1);
INSERT INTO t_child VALUES (2, 1);

You can delete rows from the CHILD table:

SQL> DELETE FROM (SELECT t_child.*
  2                 FROM t_child
  3                 JOIN t_parent ON t_parent.id = t_child.parent_id
  4                WHERE t_parent.name = 'a');

2 rows deleted
like image 6
Vincent Malgrat Avatar answered Oct 17 '22 14:10

Vincent Malgrat