Delete rows from a table with inner join conditions

Question

I have a query that selects data from a table based on some inner joins:

select * from table1 p
inner join table2 e on e.col1='YU' and e.username=p.username
inner join table3 d on p.col2=d.col3 and d.col4="IO" and d.col5=-1 and e.col3=d.col6

The output of this contains the rows from table1 that I want to delete. So I tried this:

delete from table1 p
inner join table2 e on e.col1='YU' and e.username=p.username
inner join table3 d on p.col2=d.col3 and d.col4="IO" and d.col5=-1 and e.col3=d.col6

But that is not a valid query and returns an error. How can I delete all the rows from table1 which are the result of the above SELECT statement?

• Mariano Desanze: This question seems too similar to this question on StackOverflow: stackoverflow.com/questions/12672082/…

Answer 1

If your aim is only delete rows from table1, you can re-write your query as follow:

DELETE FROM table1 p
WHERE EXISTS(
 SELECT 'MYROW'
 FROM table2 e
 JOIN table3 d
 ON d.col4 = 'IO'
 AND d.col5 = -1
 AND e.col3 = d.col6
 WHERE e.col1 = 'YU'
 AND e.username = p.username
 AND p.col2 = d.col3
);

You convert your INNER JOIN between main table (table1) and the others with using of WHERE EXISTS condition.

Note: string literals (like 'IO') need to be quoted with single quotes and not double quotes.

• ypercubeᵀᴹ: I edited the code (you had t2.col2 = t3.col3 while the OP's query had p.col2 = d.col3 i.e.: t1.col2 = t3.col3)

Answer 2

In MySQL this kind of deleting statement works:

delete p.* from table1 p
inner join table2 e on e.col1='YU' and e.username=p.username
inner join table3 d on p.col2=d.col3 and d.col4="IO" and d.col5=-1 and e.col3=d.col6

Pay attention to delete p.*.

• Andriy M: Welcome to the site! While this query may work in MySQL, your answer is irrelevant because the question is about a different database product, and in that product your answer won't work.
• David Spillett: You can do the same in MS SQL Server, and similar in postgres with its "using" clause, but IIRC Oracle does not support this. It is not in the SQL standards, hence each RBDMS that does support such things uses slightly different syntax for their extension.