1

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?

3

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.

| improve this answer | |
  • 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) – ypercubeᵀᴹ Apr 7 '16 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.*.

| improve this answer | |
  • 1
    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 '16 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. – David Spillett Apr 7 '16 at 8:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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