I am trying to update multiple columns with a single query
This is my code: I want to update the
Employee
EmployeeCode
update Employee
set EmployeeCode = 26589, EmployeeCode = 26587
where EmployeeID = 'EA45AED9-94A6-E711-AF12-E4029B75E01C'
and 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.