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 |
我希望将ID1行中的值3更改为0，但是我不知道值3是在col1、col2还是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;
这会管用，但对我来说效率很低.特别是因为我的实际数据总共包含9个相关列.我很确定，我不应该每次都要执行9个查询才能得到我想要的结果.
所以我的问题是，是否有更好、更有效的解决方案来解决这个问题？