Question 21284 in Mysql Question posted in
The official MySQL documentation can be found here.

Delete from select by many columns in MySql

Probably it’s a simple question, many people asked similar questions before, but it’s very hard to find an answer for my specific case.

I have a complex query like that:

SELECT val1, val2, val3
FROM table1
LEFT JOIN ...
WHERE ...
HAVING ...

Now I want to delete some rows from another table (let’s call it table2) after joining with the result of the previous statement. Like that:

DELETE FROM table2 WHERE field1=val1 AND field2=val2 AND field3=val3

Is it possible to do that with a single query?

Tags:

2

Answers


  1. 0

    According to MySQL 8.0 Reference Manual, section "Multi-Table Deletes" this syntax is one of the two given alternatives:

    DELETE Table2
FROM Table2
  INNER JOIN Table1
WHERE Table1.val1=Table2.val1 
  AND Table1.val2=Table2.val2
  AND Table1.val3=Table2.val3;

    Also note, that your are deleting rows and not table(s).

    Login or Signup to reply.
  2. 0

    You can use IN operator as the following:

    DELETE FROM table2 
WHERE (field1, field2, field3) IN
(
  SELECT val1, val2, val3
  FROM table1
  LEFT JOIN ...
  WHERE ...
  HAVING ...
)

    See more info about using IN operator from the MySql reference manual.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search