2

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?

1

2 Answers 2

4

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.

1
  • 1
    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) Apr 7, 2016 at 8:55
-1

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.*.

2
  • 2
    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.
    – Andriy M
    Apr 7, 2016 at 8:12
  • 2
    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. Apr 7, 2016 at 8:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.