MySQL如何与一组给定的值的更新的每一行的一列 [英] mysql how to update a column of every row with a given set of values
问题描述
我想从一组给定值的值更新为表的每一行的一列。因此,例如:
I want to update a column for every row of a table with a value from a given set of values. So for example:
id name code
---------------------
1 n1
2 n2
3 n3
和我有这个数组值的['code-1,code-2','code3'],我想设置每一行,所以对于一个行为code列的值将是'code-1'的阵列和两个排为code列的值将是'code-2'和为$ C $三连胜值C柱将'code-3'数组。所以最终的表将是这样的:
And i have this array of values ['code-1','code-2','code3'] that I want to set to every row, so for row one the value for code column will be 'code-1' from array, and row two the value for code column will be 'code-2' and for row three value for code column will be 'code-3' from array. So the final table will look like this:
id name code
---------------------
1 n1 code-1
2 n2 code-2
3 n3 code-3
我怎样才能做到这一点在一个SQL查询?
How can i do this in one sql query?
推荐答案
您可以做这样的事情:
update table t
set code = concat('code-', id)
where id in (1, 2, 3);
如果在codeS是不是真的绑ID,您可以使用情况
:
If the codes aren't really tied to the ids, you can use a case
:
update table t
set code = (case when id = 1 then 'code-1'
when id = 2 then 'code-2'
when id = 3 then 'code-3'
end)
where id in (1, 2, 3);
这篇关于MySQL如何与一组给定的值的更新的每一行的一列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!