如何更新SQL Server中的多个列？

I am trying to update multiple columns with a single query

我试图用一个查询更新多个列

This is my code: I want to update the Employee table and the table has multiple null values in EmployeeCode column that I have to update with new value.

这是我的代码：我想更新Employee表，并且该表在EmployeeCode列中有多个空值，我必须用新值更新。

update Employee 
set EmployeeCode = 26589, EmployeeCode = 26587 
where EmployeeID = 'EA45AED9-94A6-E711-AF12-E4029B75E01C' 
  and EmployeeID = '0A362F00-96A6-E711-AF12-E4029B75E01C';

4 个解决方案

#1

If you really need to, you can do it in one step, using CTE:

如果您真的需要，可以使用CTE一步完成：

WITH Pairs (code, id)
AS (
  SELECT 26589 AS code, 'EA45AED9-94A6-E711-AF12-E4029B75E01C' AS id
  UNION ALL
  SELECT 26587 AS code, '0A362F00-96A6-E711-AF12-E4029B75E01C' AS id
)
UPDATE Employee set EmployeeID = Pairs.id
FROM Pairs
INNER JOIN Employee ON (pairs.code = Employee .EmployeeCode)

Explanation:

说明：

the WITH Pairs (...) AS (...) part defines the code - id pairs you need. You can use as many SELECT -s here, as you need. Just keep UNION -ing them.
WITH Pairs（...）AS（...）部分定义了您需要的代码 - id对。您可以根据需要在此处使用尽可能多的SELECT。只要保持UNION - 他们。
After the WITH you can update your Employee table. All you need to do is to do an INNER JOIN on your Pairs subquery. So you'll update only those rows that `you want to.
在WITH之后，您可以更新Employee表。您需要做的就是在Pairs子查询上进行INNER JOIN。所以你只会更新你想要的那些行。

#2

Two separate updates is the simplest way:

两个单独的更新是最简单的方法：

update Employee
    set EmployeeCode = 26589
    where EmployeeID = 'EA45AED9-94A6-E711-AF12-E4029B75E01C' ;

update Employee
    set EmployeeCode = 26587
    where EmployeeID = '0A362F00-96A6-E711-AF12-E4029B75E01C';

You can wrap this in a transaction so they are effective at the same time.

您可以将其包装在事务中，以便它们同时有效。

You can merge them into one statement:

您可以将它们合并为一个语句：

update Employee
    set EmployeeCode = (case when EmployeeID = 'EA45AED9-94A6-E711-AF12-E4029B75E01C'26589 then 26589 else 26587 end)
    where EmployeeID in ('EA45AED9-94A6-E711-AF12-E4029B75E01C', '0A362F00-96A6-E711-AF12-E4029B75E01C');

But that seems unnecessary.

但这似乎没必要。

If you have more than two, then this might be recommended:

如果您有两个以上，那么可能会建议：

update e
    set EmployeeCode = v.EmployeeCode
    from Employee e join
         (values ('EA45AED9-94A6-E711-AF12-E4029B75E01C', 26589),
                 ('0A362F00-96A6-E711-AF12-E4029B75E01C', 26587)
         ) v(EmployeeId, EmployeeCode)
         on e.EmployeeId = v.EmployeeId;

#3

Use CASE statement

使用CASE语句

UPDATE Employee
SET    EmployeeCode = CASE EmployeeID
                        WHEN 'EA45AED9-94A6-E711-AF12-E4029B75E01C' THEN 26589
                        ELSE 26587
                      END
WHERE  EmployeeID IN ( 'EA45AED9-94A6-E711-AF12-E4029B75E01C', '0A362F00-96A6-E711-AF12-E4029B75E01C' );

Note : I have assumed 'EA45AED9-94A6-E711-AF12-E4029B75E01C' maps to 26589 code, if not swap the values

注意：我假设'EA45AED9-94A6-E711-AF12-E4029B75E01C'映射到26589代码，如果没有交换值

#4

Separate your SQL-statements

分开你的SQL语句

UPDATE Employee
SET EmployeeCode = 26589,
WHERE EmployeeID = 'EA45AED9-94A6-E711-AF12-E4029B75E01C'

UPDATE Employee
SET EmployeeCode = 26587
WHERE EmployeeID = '0A362F00-96A6-E711-AF12-E4029B75E01C';

#1