Update using WITH statement

[tensi4u]

Hi all,
I've been in stuck on updating a table. Here is the query.

with
tab_one ( "col_1", "col_2", "col_3", "col_4" ) as (
select ax."col1", ax."col2", bx."col1", bx."col2"
from schm1.tab_a ax, schm2.tab_b bx
where ~~
),
tab_two ( "col_1", "col_2", "col_3", "col_4" ) as (
select axx."col_1", axx."col_2", axx."col_3", axx."col_4"
from tab_one axx, tab_one bxx
where ~~~
)

UPDATE schm1.tab_a tg
SET tg."col_Y" = t2."col_3"
FROM schm1.tab_a tg, tab_two t2
WHERE tg."col_X" = t2."col_1";

What did I screw up?

[earl60]

You will better work using the merge statement,
something like :

merge into table_cible c
using ( select o.col1, t.coln from table_one o
inner join table_two t
on t.col1 = o.col1 ) s
on s.col1 = c.col1
when matched and c.col2 = 1 then
update set c.coln = s.coln.