Hi. I have following scenario:
Table A: columns :
c1 c2 Key1
----------------
'a' '1' 'X'
'b' '2' 'X'
'c' '3' 'X'
'a' '4' 'P'
'b' '5' 'P'
'c' '6' 'P'
=============

Table B: Columns:
f1 f2 f3 Key2
----------------------
'1' '2' '3' 'Y'
'4' '5' '6' 'Z'
=================

Table C: Columns:
Key1 Key2
-----------------
'X' ?
=============


I would like to update C.Key2 with the value of B.Key2 for the condition such
that (C.Key1 = A.Key1 and A.c1 = 'a' and A.c2 = B.f1)
and (C.Key1 = A.Key1 and A.C1 = 'b' and A.c2 = B.f2)
and (C.Key1 = A.Key1 and A.C1 = 'c' and A.c2 = B.f3)

I came up with the solution below but wondering if there is better way to do it , for example, can I perform the update with the combination of some sort of JOINs:

My solution:
Created a view temp out of Table A as follows:

Table Temp: Columns:
n1 n2 n3 n4
--------------------
'1' '2' '3' 'X'
'4' '5' '6' 'P'
===============

then
Update C set C.key2 = B.key2 where
C.key1 = temp.n4 and temp.n1 = B.f1 and temp.n2 = B.f2
and temp.n3 = B.f3

Will greatly appreciate any suggestions.

Have a look at the MERGE statement. Alternatively, you may want to read up on "scalar subselects".

Hi taru,

you can do it with one statement:

Test:

That will change every row in the table because you do not have a WHERE clause on the outer UPDATE itself. If that is desired... fine.

Hi stolze,

what do you mean of "WHERE clause on the outer UPDATE itself"? According to the taru's example we should change all records in the table c. I have found no restrictions on c in the text.

I didn't say that anything was wrong with your statement, did I? I was just stating that it may not be applicable to all situations.