Delete from select by many columns in MySql-CodePudding

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 some complex query, something like that:

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

Now I want to delete another table (let's call it table2) by few columns. Like that:

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

Is it possible to do that with a single quesry?

CodePudding user response：

You are deletion not a Table(s), but the rows.

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

CodePudding user response：

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.