multi row update in one SQL statment

[Rima]

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.

[Nicolas Payre]

Re: multi row update in one SQL statment


"Rima" <parikhrima@yah oo.com> wrote in message
news:8a126d63.0 307251222.2b111 47e@posting.goo gle.com...[color=blue]
> 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.[/color]

update A a
set (a.commit_id, a.capital_marke t_id, a.chg_lst_date) = (
select b.commit_id, b.capital_marke t_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
)

[Nic]

Re: multi row update in one SQL statment


"Rima" <parikhrima@yah oo.com> wrote in message
news:8a126d63.0 307281059.44a09 a8d@posting.goo gle.com...[color=blue]
> "Nicolas Payre" <nicpayre[junk]@sympatico.ca> wrote in message[/color]
news:<TTlUa.887 8$1I5.1134904@n ews20.bellgloba l.com>...[color=blue][color=green]
> > "Rima" <parikhrima@yah oo.com> wrote in message
> > news:8a126d63.0 307251222.2b111 47e@posting.goo gle.com...[color=darkred]
> > > 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.[/color]
> >
> > update A a
> > set (a.commit_id, a.capital_marke t_id, a.chg_lst_date) = (
> > select b.commit_id, b.capital_marke t_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[/color][/color]
=[color=blue][color=green]
> > b.commit_id
> > )
> > )
> > where a.chg_lst_date < (select max(b2.chg_lst_ date)
> > from B b2
> > where b2.commit_id = a.commit_id
> > )[/color]
>
> Thanks Nicolas.
>[/color]
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_marke t_id, a.chg_lst_date) = (
select b.commit_id, b.capital_marke t_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
)

[color=blue]
> 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_marke t_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?[/color]

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.


[color=blue]
> Thanks,
> Rima.[/color]