I have these 2 tables
Table P
A | B | C | D
-----------------------------
'abc' | NULL | 'kkk' | NULL
'xyz' | NULL | 'www' | NULL
'pqr' | NULL | 'ccc' | NULL
Table Q
X | Y
-----------------------------
'abc' | 123
'xyz' | 12
'pqr' | 34
I need to update Table P column B and D with the value of Y from Table Q as long as the column A and C in Table P matches values of column X in Table Q.
My UPDATE now:
UPDATE TableP
SET B = Q.Y
FROM TableQ Q (NOLOCK)
WHERE Q.X = A
UPDATE TableP
SET D = Q.Y
FROM TableQ Q (NOLOCK)
WHERE Q.X = C
I only know how to do it in 2 UPDATE statements, how do I do this in a single UPDATE statement?
You could use UPDATE FROM JOIN:
UPDATE p
SET B = q1.Y, --COALESCE(q1.Y, p.B) if needed
D = q2.Y --COALESCE(q2.Y, p.D)
FROM TableP p
LEFT JOIN TableQ q1
ON p.A = q1.X
LEFT JOIN TableQ q2
ON p.C = q2.X;
Output:
╔═════╦═════╦═════╦═══╗
║ A ║ B ║ C ║ D ║
╠═════╬═════╬═════╬═══╣
║ abc ║ 123 ║ kkk ║ ║
║ xyz ║ 12 ║ www ║ ║
║ pqr ║ 34 ║ ccc ║ ║
╚═════╩═════╩═════╩═══╝
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments