SQL更新多行，每行都有条件[关闭]

Question

< b >想改进这个问题？通过编辑此帖子添加详细信息并澄清问题。

我有一个包含列A，B，C的表。

如果A和B（A和B一起是唯一的）我想更新列C，因此伪代码如下所示：

update table 
set (a = 1, b = 1, c = 1000)
    (a = 2, b = 2, c = 2000)
    (a = 3, b = 3, c = 3000)
where a and b matches columns

我该如何用SQL编写？

Answer 1

如果是指 a 等于 b 时：

UPDATE t 
SET c = 1000 * a
WHERE a = b;

对于任何rdbms。
参见演示

Answer 2

多重更新适用于任何RDBMS

update yourtable set c = 1000 where a = 1 and b = 1
update yourtable set c = 2000 where a = 2 and b = 2
update yourtable set c = 3000 where a = 3 and b = 3

人们会假设UPDATE语句应该是非常标准的。
但是当从表或子查询更新时，语法可能会有所不同。

这在MS Sql Server中有效

update t
set c = q.c
from yourtable t
join (values 
   (1, 1, 1000)
  ,(2, 2, 2000)
  ,(3, 3, 3000)
) q(a, b, c)
on t.a = q.a and t.b = q.b

这在Postgresql中有效

update yourtable t
set c = q.c
from 
(values 
   (1, 1, 1000)
  ,(2, 2, 2000)
  ,(3, 3, 3000)
) q(a, b, c)
where q.a = t.a and q.b = t.b

这在MySql中工作

update yourtable t
join 
(
   select 1 as a, 1 as b, 1000 as c
   union all select 2, 2, 2000
   union all select 3, 3, 3000
) q on q.a = t.a and q.b = t.b
set t.c = q.c

这适用于Oracle RDBMS

update yourtable t 
set t.c = 
(
   select q.c
   from 
   (
      select 1 as a, 1 as b, 1000 as c from dual
      union all select 2, 2, 2000 from dual
      union all select 3, 3, 3000 from dual
   ) q
   where q.a = t.a and q.b = t.b
)

Answer 3

我认为您可以使用< code>case表达式:

update table 
    set c = (case when a = 1 and b = 1 then 1000
                  when a = 2 and b = 2 then 2000
                  when a = 3 and b = 3 then 3000
             end)
where (a = 1 and b = 1) or (a = 2 and b = 2) or (a = 3 and b = 3);