Re: multi row update in one SQL statment

"Rima" <parikhrima_at_yahoo.com> wrote in message news:8a126d63.0307281059.44a09a8d_at_posting.google.com... > "Nicolas Payre" <nicpayre[junk]_at_sympatico.ca> wrote in message  news:<TTlUa.8878$1I5.1134904_at_news20.bellglobal.com>...
> > "Rima" <parikhrima_at_yahoo.com> wrote in message
> > news:8a126d63.0307251222.2b11147e_at_posting.google.com...
> > > 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
>                        )

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. Received on Tue Jul 29 2003 - 05:09:54 CEST