kasdega kasdega - 5 months ago 15
SQL Question

Mysql update a row if its the only matching result

I have a table like so:

Item
---------
id
parentId
name
inventoryNumber


I need to update the inventoryNumber if and only if the row...


  1. parentId = 12345

  2. name string matches exactly a given string "The
    quick brown fox"

  3. the row is the only row that matches the first two
    criteria.



I believe I know how to do this if I was just doing a select...

SELECT * FROM Item WHERE parentId=12345
AND name LIKE 'The quick brown fox' HAVING count(*)=1


but I need to update the row...

UPDATE Item SET inventoryNumber = 456 WHERE
this is the only row where parentId=12345 and the only row where name='The quick brown fox'

I have about 5000 of these rows to update so it would greatly reduce my workload if I can get a way to update in a single statement. Can this be done?

UPDATE: I already tried putting this in a subquery like:

UPDATE Item SET inventoryNumber = 456 WHERE id IN (
SELECT id
FROM Item
WHERE parentId = 12345 AND name LIKE 'The quick brown fox' HAVING count(*)=1
);


But I get an error from MySql when I do that "You can't specify target table 'item' for update in FROM clause:

Answer

How about using a subquery:

EDIT

CREATE TABLE Item_tmp LIKE Item;
INSERT INTO Item_tmp SELECT * FROM Item;

UPDATE Item SET inventoryNumber = 456 WHERE id IN (
   SELECT id 
   FROM Item_tmp
   WHERE parentId = 12345 AND name LIKE 'The quick brown fox' HAVING count(*)=1
);