Home How to efficiently update a MySQL row by unique value within an unknown column

Questions

How to efficiently update a MySQL row by unique value within an unknown column

April 8, 2022

Explanation
I have a bunch of columns that don’t have a unique constraint, since multiple rows can contain the same values. However, each row will have unique values (with the exception of zero) within the columns I’m trying to update.

Example

| ID | col1 | col2 | col3 |
|----|------|------|------|
| 1  | 2    | 3    | 4    |
| 2  | 2    | 4    | 3    |

I wish to change value 3 to 0 in row ID 1, however I don’t know whether value 3 is within col1, col2 or col3.

What I thought of myself
A possible solution would be:

UPDATE table SET col1 = 0 WHERE ID = 1 AND col1 = 3;
UPDATE table SET col2 = 0 WHERE ID = 1 AND col2 = 3;
UPDATE table SET col3 = 0 WHERE ID = 1 AND col3 = 3;

This will work, but it seems very inefficient to me. Especially since my actual data contains a total of 9 relevant columns. I’m pretty sure I shouldn’t have to perform 9 queries each time just to get the result I’m looking for.

So my question is if there’s a better, more efficient solution to solve the problem?

>Solution :

From your description, you can try to use IF with your update logic otherwise use the original value in one statement.

UPDATE table 
SET col1 = IF(col1 = 3,0,col1),
    col2 = IF(col2 = 3,0,col2),
    col3 = IF(col3 = 3,0,col3)  
    --...
WHERE ID = 1

If you already setted indexes on your update column you can try to add that in where filter which might get better performance

UPDATE table 
SET col1 = IF(col1 = 3,0,col1),
    col2 = IF(col2 = 3,0,col2),
    col3 = IF(col3 = 3,0,col3)  
    --...
WHERE ID = 1 AND 
(col1 = 3) OR
(col2 = 3) OR
(col3 = 3)