MySQL Query Multiple Delete Values

Question

Please help. My eyes are going numb. My body weak.

Attempt #9432

Taken from this answer MySQL delete multiple rows in one query conditions unique to each row

DELETE FROM `productimages` WHERE (`ID`,`imageURL`) 
    (179,'http://www.example.com/example1.jpg')
    (179,'http://www.example.com/example2.jpg')
    (179,'http://www.example.com/example3.jpg')
    (179,'http://www.example.com/example4.jpg')
    (179,'http://www.example.com/example5.jpg'))

Failed.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '((179,'https:// . . .

Referencing my archive, on 02/14/96 - on attempt #387 I discovered that you cannot use multiple WHERE clauses in one query. I next tried

Attempt #837

DELETE FROM `productimages` (WHERE `productID` = 179 AND `imageURL` = 'http://www.example.com/example1.jpg') 
(AND `productID` = 179 AND `imageURL` = 'http://www.example.com/example2.jpg')
(AND `productID` = 179 AND `imageURL` = 'http://www.example.com/example3.jpg')
(AND `productID` = 179 AND `imageURL` = 'http://www.example.com/example4.jpg')
(AND `productID` = 179 AND `imageURL` = 'http://www.example.com/example5.jpg') 
(AND `productID` = 179 AND `imageURL` = 'http://www.example.com/example6.jpg')

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(WHERE 'productID' = 179 AND 'imageURL' = 'https:// . . .

A more recent attempt, my closest yet:

Attempt #10473

DELETE FROM `productimages` 
WHERE `productID` = 179 
AND `imageURL` = 'http://www.example.com/example1.jpg' 
AND `imageURL` = 'http://www.example.com/example2.jpg' 
AND `imageURL` = 'http://www.example.com/example3.jpg' 
AND `imageURL` = 'http://www.example.com/example4.jpg' 
AND `imageURL` = 'http://www.example.com/example5.jpg' 
AND `imageURL` = 'http://www.example.com/example6.jpg'

Indescribable joy. A successfully query.

Upon closer look...

0 rows affected. (Query took 0.0128 seconds.)

Again, defeated.

Has anyone gotten this far yet? You'd think there'd be a manual or something.

GMB · Accepted Answer · 2019-02-28T01:10:08.457

1

It is possible to check for the value of a tuple with an IN condition. You just have syntax errors.

Syntax is as follows :

DELETE FROM `productimages` 
WHERE (`productID`,`imageURL`) IN (
    (179,'http://www.example.com/example1.jpg'),
    (179,'http://www.example.com/example2.jpg'),
    (179,'http://www.example.com/example3.jpg'),
    (179,'http://www.example.com/example4.jpg'),
    (179,'http://www.example.com/example5.jpg')
)

This is quite a flexible way to filter records, and would fit the case where there several different IDs in the tuples. Efficiency, however, will decrease with the number of tuples in the list, and eventually reach a system limit. To process a very large list, you would better consider using a temp table that you can simply join with.

edited Feb 28 '19 at 01:10

answered Feb 28 '19 at 00:22

GMB

216,147
25
84
135

Remarkable. It was my mistake leaving the `ID` in Attempt #9432, it should have been `productID` as the rest. With that said, `WHERE productID = 179` in your query can be removed, along with a replace of the `AND` with `WHERE`, finally additionally adding a comma `),` as the first to every other but the last, and .. it works. It finally works. Thank you @GMB. Thank you. – bbruman Feb 28 '19 at 00:45
@bbruman : welcome ! I fixed my answer accordingly (you could have edited it directly, too). – GMB Feb 28 '19 at 01:10

Ibu · Answer 2 · 2019-02-28T00:24:02.630

The problem is that in your WHERE clause you are asking to delete where the id is one value and the image url is all the other values at the same time.

What you need to do is use OR for each value of the imageURL:

DELETE FROM `productimages` WHERE `productID` = 179 AND (
  `imageURL` = 'http://www.example.com/example1.jpg' 
  OR `imageURL` = 'http://www.example.com/example2.jpg' 
  OR `imageURL` = 'http://www.example.com/example3.jpg' 
  OR `imageURL` = 'http://www.example.com/example4.jpg' 
  OR `imageURL` = 'http://www.example.com/example5.jpg' 
  OR `imageURL` = 'http://www.example.com/example6.jpg'
)

You can also use the IN operator:

DELETE FROM `productimages` 
WHERE `productID` = 179 AND `imageURL` IN (
  'http://www.example.com/example1.jpg', 
  'http://www.example.com/example2.jpg', 
  'http://www.example.com/example3.jpg', 
  ...
)

Also: The reason your first one isn't working is because you are missing a parentheses and commas.

DELETE FROM `productimages` WHERE (`ID`,`imageURL`) (
  (179,'http://www.example.com/example1.jpg'),
  (179,'http://www.example.com/example2.jpg'),
  (179,'http://www.example.com/example3.jpg'),
  (179,'http://www.example.com/example4.jpg'),
  (179,'http://www.example.com/example5.jpg'),
)

Thank you for the attempts @Ibu. Your first query worked to delete one image (the first one), but no others. Your second had the same result as my last one, no error, but 0 rows affected. — bbruman, Feb 28 '19 at 00:23
Close on the third. Thank you again. I'll add three more attempts to the book. — bbruman, Feb 28 '19 at 00:33

MySQL Query Multiple Delete Values

2 Answers2