Using a query in an update I have a query which generates a result set (key, a, b).

I want to use an update statement where, for each entry in a table with Tkey = key, set ColA = a, ColB = b.

How would I do this?

Right now I'm trying:

UPDATE table1
set ColA = a,
ColB = b
where TKey in (select TKey, a, b from ...)


but a and b can't reference the where clause, right? So how can I do this?
I'm a big fan of MERGE.
You can try the following;

MERGE INTO table1 main
USING ( SELECT TKey, a, b
          FROM table2 ) subsel
ON (main.TKey = subsel.TKey) 
WHEN MATCHED THEN
UPDATE SET main.ColA = subsel.a,
          main.ColB = subsel.b


Here's a quick example;

SQL&gt; SELECT * FROM maintab;

TKEY                 COLA                 COLB
-------------------- -------------------- --------------------
rec1
rec2
rec3

SQL&gt; SELECT * FROM updtable;

TKEY                 A                    B
-------------------- -------------------- --------------------
rec1                 123                  456
rec2                 789                  321
rec3                 654                  987

SQL&gt; l
  1  MERGE INTO maintab main
  2  USING ( SELECT TKey, a, b
  3            FROM updtable ) subsel
  4  ON (main.TKey = subsel.TKey)
  5  WHEN MATCHED THEN
  6  UPDATE SET main.ColA = subsel.a,
  7*            main.ColB = subsel.b
SQL&gt; /

3 rows merged.

SQL&gt; SELECT * FROM maintab;

TKEY                 COLA                 COLB
-------------------- -------------------- --------------------
rec1                 123                  456
rec2                 789                  321
rec3                 654                  987