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

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?

CodePudding user response：

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)