sql server - UPDATE 2 columns of a table of values from a table -

i have these 2 tables

table p      | 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 update table p column b , d value of y table q long column , c in table p matches values of column x in table q.

my update now:

update tablep  set b = q.y tableq q (nolock) q.x =  update tablep  set d = q.y tableq q (nolock) q.x = c 

i know how in 2 update statements, how do in single update statement?

you use update join:

update p set b = q1.y,        --coalesce(q1.y, p.b) if needed     d = q2.y         --coalesce(q2.y, p.d) tablep p left join tableq q1   on p.a = q1.x left join tableq q2   on p.c = q2.x; 

livedemo

output:

╔═════╦═════╦═════╦═══╗ ║   ║  b  ║  c  ║ d ║ ╠═════╬═════╬═════╬═══╣ ║ abc ║ 123 ║ kkk ║   ║ ║ xyz ║  12 ║ www ║   ║ ║ pqr ║  34 ║ ccc ║   ║ ╚═════╩═════╩═════╩═══╝