更新语句以更新多行

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';

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.

我也尝试了这个，我也试过使用AND语句。都没有奏效。基本上我正在寻找一种不那么新手的方式然后上面的方法，如果存在的话。我花了很长时间搜索，却找不到一个。

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

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

对这个提出任何建议，顺便说一句，我使用的是sql server。谢谢

4 个解决方案

#1

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

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

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

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

#2

I am looking for a less newbie way

我正在寻找一种不那么新手的方式

Doing two separate update statements is (according to me) "the less newbie way" you could complicate stuff and do something like this.

做两个单独的更新语句是（根据我）“新手少”的方式，你可以使事情复杂化并做这样的事情。

update employee
set hire_date = case emp_id
                  when 'PMA42628M' then '1979-03-15'
                  when 'PSA89086M' then '1988-12-22'
                end
where emp_id in ('PMA42628M', 'PSA89086M')

but what would that gain you? The entire update would run in one implicit transaction so if you want your two updates to be in a transaction you just use begin transaction .... commit.

但那会有什么收获呢？整个更新将在一个隐式事务中运行，因此如果您希望两个更新都在事务中，则只需使用begin transaction .... commit。

#3

To add to the other ways already mentioned: you can make a temporary table or a table variable containing the updates you want to do, then run the UPDATE statement linking the table to the table you intend to update.

要添加到已提到的其他方法：您可以创建包含要执行的更新的临时表或表变量，然后运行将表连接到要更新的表的UPDATE语句。

Note that for two updates, you get two statements: the INSERT into the update table and the UPDATE statement itself. The number of statements remains two though for as many updates you need to do.

请注意，对于两个更新，您将获得两个语句：INSERT到更新表和UPDATE语句本身。尽管您需要进行多次更新，但语句数仍为2。

CREATE TABLE #employee (emp_id VARCHAR(9) NOT NULL PRIMARY KEY,hire_date DATE NOT NULL);
INSERT INTO #employee (emp_id,hire_date)
VALUES ('PMA42628M','2013-06-05'),('PSA89086M','2013-06-05');

CREATE TABLE #target_updates(emp_id VARCHAR(9) NOT NULL,hire_date DATE NOT NULL);
INSERT INTO #target_updates (emp_id,hire_date)
VALUES ('PMA42628M','1979-03-15'),('PSA89086M','1988-12-22');

UPDATE
    #employee
SET
    hire_date=tu.hire_date
FROM
    #employee AS e
    INNER JOIN #target_updates AS tu ON
        tu.emp_id=e.emp_id;

SELECT
    *
FROM
    #employee
ORDER BY
    emp_id;

DROP TABLE #target_updates;
DROP TABLE #employee;

#4

update table_name set='value' where orgid in (idnum1, idnum2)

update table_name set ='value'where orgid in（idnum1，idnum2）

#1