更新语句以更新多行 [英] Update statement to update multiple rows

问题描述

我对以下语法有疑问.是否有一种更简洁的方法可以将其汇总为一个语句而不是两个语句.我已经尝试了几次迭代，但这似乎是我可以成功执行这两个语句的唯一方法.

I have a question regarding the following syntax. Is there a cleaner way to roll this up into one statement rather than two. I've tried several iterations but this seems to be the only way I can successfully execute these two statements.

UPDATE employee
SET hire_date = '1979-03-15'
WHERE emp_id = 'PMA42628M' 

UPDATE employee
SET hire_date = '1988-12-22'
where emp_id = 'PSA89086M'; 

我也试过这个，我也试过使用 AND 语句.都没有工作.基本上，我正在寻找一种比上述方法少的新手方法，如果存在的话.我找了半天也没找到.

I tried this as well and I also tried using an AND statement. Neither worked. Basically I am looking for a less newbie way then the method above, if one exists. I spent a long time searching and did not find one.

UPDATE employee
SET hire_date = ('1979-03-15', '1988-12-22')
WHERE emp_id = ('PMA42628M', 'PSA89086M');

对此有任何建议，顺便说一下，我正在使用 sql server.谢谢

Appriciate any advice on this one, and by the way, I am using sql server. Thanks

推荐答案

试试这个，这将组合多个选择并返回它们，就像它们来自数据库一样:

Try this one, this will combine multiple selects and returns them as if they come from the DB:

UPDATE e
SET hire_date = t.hire_date
FROM dbo.employee e
JOIN (
    SELECT emp_id = 'PMA42628M', hire_date = '1979-03-15'
    UNION ALL
    SELECT emp_id = 'PSA89086M', hire_date = '1988-12-22'
) t ON t.emp_id = e.emp_id

如果您使用的是 SQL Server 2008 或更高版本，您还可以对派生表使用不同的语法:

If you are using SQL Server 2008 or later version, you could also use a different syntax for the derived table:

UPDATE e
SET hire_date = t.hire_date
FROM dbo.employee e
JOIN (
    VALUES
        ('PMA42628M', '1979-03-15'),
        ('PSA89086M', '1988-12-22')
) t (emp_id, hire_date) ON t.emp_id = e.emp_id

这篇关于更新语句以更新多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！