MySQL delete multiple rows in one query conditions unique to each row

Question

So I know in MySQL it s possible to insert multiple rows in one query like so   INSERT INTO table  col1 col2  VALUES  1 2   3 4   5 6    I would like to delete multiple rows in a similar way  I know it s possible to delete multiple rows based on the exact same conditions for each row  i e   DELETE FROM table WHERE col1  4  and col2  5    or  DELETE FROM table WHERE col1 IN  1 2 3 4 5    However  what if I wanted to delete multiple rows in one query  with each row having a set of conditions unique to itself  Something like this would be what I am looking for   DELETE FROM table WHERE  col1 col2  IN  1 2   3 4   5 6    Does anyone know of a way to do this  Or is it not possible

User · Accepted Answer

You were very close  you can use this   DELETE FROM table WHERE  col1 col2  IN   1 2   3 4   5 6     Please see this fiddle

User · Answer

A slight extension to the answer given  so  hopefully useful to the asker and anyone else looking   You can also SELECT the values you want to delete  But watch out for the Error 1093 - You can t specify the target table for update in FROM clause   DELETE FROM     orders products history WHERE      branchID  action  IN       SELECT         branchID          action     FROM                   SELECT             branchID              action         FROM             orders products history         GROUP BY             branchID              action         HAVING             COUNT     gt  10000           a          I wanted to delete all history records where the number of history records for a single action branch exceed 10 000  And thanks to this question and chosen answer  I can   Hope this is of use   Richard

User · Answer

Took a lot of googling but here is what I do in Python for MySql when I want to delete multiple items from a single table using a list of values   create some empty list values       continue to append the values you want to delete to it  BUT you must ensure instead of a string it s a single value tuple values append   Your Variable      Then once your array is loaded perform an execute many cursor executemany  quot DELETE FROM YourTable WHERE ID    s quot   values

[mysql] MySQL delete multiple rows in one query conditions unique to each row

Examples related to mysql

Examples related to unique-constraint

Examples related to multiple-records