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.
update Employee
set EmployeeCode = 26589, EmployeeCode = 26587
where EmployeeID = 'EA45AED9-94A6-E711-AF12-E4029B75E01C'
and EmployeeID = '0A362F00-96A6-E711-AF12-E4029B75E01C';
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;
Use CASE statement
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
Separate your SQL-statements
UPDATE Employee
SET EmployeeCode = 26589,
WHERE EmployeeID = 'EA45AED9-94A6-E711-AF12-E4029B75E01C'
UPDATE Employee
SET EmployeeCode = 26587
WHERE EmployeeID = '0A362F00-96A6-E711-AF12-E4029B75E01C';
If you really need to, you can do it in one step, using 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:
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 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.