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

multi row update in one SQL statment

P: n/a
I have the following two tables :

table a
(commit_id,
capital_market_id,
chg_lst_date
)

table b
(b_seq_id,
commit_id,
capital_market_id,
chg_lst_date
)

commid_id is PK in A but not in B. B can have multiple entries per
commit_id.

I want to update all entries in table A - set the capital_market_id
and chg_lst_date - from the corresponding commit_id entry in table B
with the following two rules :

1. since B can have multiple rows per commit_id I want to pick the one
with the latest txn_time and
2. only update in A if the chg_lst_date of a is less than that of B.

Can I do this in one update statement? or do I have to do a
cursor/loop ?

I originally posted this in oracle.misc but then realized it might be
a non-technical forum - hence this repeat post.
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"Rima" <pa********@yahoo.com> wrote in message
news:8a**************************@posting.google.c om...
I have the following two tables :

table a
(commit_id,
capital_market_id,
chg_lst_date
)

table b
(b_seq_id,
commit_id,
capital_market_id,
chg_lst_date
)

commid_id is PK in A but not in B. B can have multiple entries per
commit_id.

I want to update all entries in table A - set the capital_market_id
and chg_lst_date - from the corresponding commit_id entry in table B
with the following two rules :

1. since B can have multiple rows per commit_id I want to pick the one
with the latest txn_time and
2. only update in A if the chg_lst_date of a is less than that of B.

Can I do this in one update statement? or do I have to do a
cursor/loop ?

I originally posted this in oracle.misc but then realized it might be
a non-technical forum - hence this repeat post.


update A a
set (a.commit_id, a.capital_market_id, a.chg_lst_date) = (
select b.commit_id, b.capital_market_id, b.chg_lst_date
from B b
where b.chg_lst_date = ( select max(b1.chg_lst_date)
from B b1
where b1.commit_id =
b.commit_id
)
)
where a.chg_lst_date < (select max(b2.chg_lst_date)
from B b2
where b2.commit_id = a.commit_id
)



Jul 19 '05 #2

P: n/a
Nic

"Rima" <pa********@yahoo.com> wrote in message
news:8a**************************@posting.google.c om...
"Nicolas Payre" <nicpayre[junk]@sympatico.ca> wrote in message news:<TT********************@news20.bellglobal.com >...
"Rima" <pa********@yahoo.com> wrote in message
news:8a**************************@posting.google.c om...
I have the following two tables :

table a
(commit_id,
capital_market_id,
chg_lst_date
)

table b
(b_seq_id,
commit_id,
capital_market_id,
chg_lst_date
)

commid_id is PK in A but not in B. B can have multiple entries per
commit_id.

I want to update all entries in table A - set the capital_market_id
and chg_lst_date - from the corresponding commit_id entry in table B
with the following two rules :

1. since B can have multiple rows per commit_id I want to pick the one
with the latest txn_time and
2. only update in A if the chg_lst_date of a is less than that of B.

Can I do this in one update statement? or do I have to do a
cursor/loop ?

I originally posted this in oracle.misc but then realized it might be
a non-technical forum - hence this repeat post.


update A a
set (a.commit_id, a.capital_market_id, a.chg_lst_date) = (
select b.commit_id, b.capital_market_id, b.chg_lst_date
from B b
where b.chg_lst_date = ( select max(b1.chg_lst_date)
from B b1
where b1.commit_id = b.commit_id
)
)
where a.chg_lst_date < (select max(b2.chg_lst_date)
from B b2
where b2.commit_id = a.commit_id
)


Thanks Nicolas.

Sorry, ithink a made a mistake the query in my initial post should have
been:

==>> See, i added a very important clause ;-)

update A a
set (a.commit_id, a.capital_market_id, a.chg_lst_date) = (
select b.commit_id, b.capital_market_id, b.chg_lst_date
from B b
where b.chg_lst_date = ( select max(b1.chg_lst_date)
from B b1
where b1.commit_id
=b.commit_id
)
==>> and b.commit_id = a.commit_id
)
where a.chg_lst_date < (select max(b2.chg_lst_date)
from B b2
where b2.commit_id = a.commit_id
)

I'm a little confused however, according to the docs in the case of

update ... set (col,col,col...) = (subquery)

the subquery must return exactly one row.

your subquery :

select b.commit_id, b.capital_market_id, b.chg_lst_date
from B b
where b.chg_lst_date = ( select max(b1.chg_lst_date)
from B b1
where b1.commit_id = b.commit_id
)
would return multiple rows. How does that work?
it depend on your data!

For exemple, if for a same commit_id you can have multiple row with the
exact same chg_lst_date, then the subquery will return multiple rows.... You
have to know what the data is, or will be. If your not sure, it better to go
with some PL/SQL where you can manage differents case with somme IF/ELSE or
even EXCEPTIONS logic.

The use of constraints will help you ensure what your data is.
Thanks,
Rima.

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.