By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,682 Members | 1,071 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,682 IT Pros & Developers. It's quick & easy.

Update using WITH statement

P: 11
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?
Oct 24 '08 #1
Share this Question
Share on Google+
1 Reply


P: 2
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.
Oct 31 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.