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-28 01:10:08Z

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, 2019 at 1:10

answered Feb 28, 2019 at 0:22

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bbruman Over a year ago

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.

GMB Over a year ago

@bbruman : welcome ! I fixed my answer accordingly (you could have edited it directly, too).

Ibu · Accepted Answer · 2019-02-28 00:24:02Z

0

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'),
)

edited Feb 28, 2019 at 0:24

answered Feb 28, 2019 at 0:17

Ibu

44k14 gold badges82 silver badges109 bronze badges

2 Comments

bbruman Over a year ago

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 Over a year ago

Close on the third. Thank you again. I'll add three more attempts to the book.

Collectives™ on Stack Overflow

MySQL Query Multiple Delete Values

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related