Re: How to update multiple rows in table

i've not done many of these types of updates before, and i don't have your tables here, but i'll take a quick stab at it. i don't think you want to name the bridge table in the subselect. by removing it, the reference to bridge.old_userid may be correlated back to the table named in the update.

  UPDATE bridge

     SET bridge.contact_data =
         ( select com_user_contacts.contact_data
             from com_user_contacts
            where com_user_contacts.userid = bridge.old_userid
         )
   WHERE bridge.old_userid in
         ( select t1.userid
             from com_user_contacts t1
         );

HTH and if it doesn't, don't forget to issue a rollback.

<twoplustwo_at_my-deja.com> wrote in message news:94v1se$2us$1_at_nnrp1.deja.com...
> What a day ... I've written SQL statements for years, but do you think
> I can figure this one out (brian dead)
> I have two tables - bridge and com_user_contacts, all I want to do is
> update the one column in bridge (contact_data) with the contact_data
> column from com_user_contacts. I DO WANT to update multiple rows in
> the bridge table but for the life of me can't figure out how to write
> the update statement... Can some tell me what I am missing????
> Below are some of my attempts....
>
>
> Thanks
>
> TwoPlusTwo
>
>
> --- more than 1 row return
> ORA-01427: single-row subquery returns more than one row
> UPDATE bridge
> SET bridge.contact_data = any
> ( select com_user_contacts.contact_data
> from com_user_contacts, bridge
> where com_user_contacts.userid = bridge.old_userid )
> WHERE exists ( select t1.userid
> from com_user_contacts t1
> where t1.userid = bridge.old_userid );
>
>
> -- ORA-01427: single-row subquery returns more than one row
> UPDATE bridge
> SET bridge.contact_data =
> ( select com_user_contacts.contact_data
> from com_user_contacts, bridge
> where com_user_contacts.userid = bridge.old_userid )
> WHERE old_userid in ( select t1.userid
> from com_user_contacts t1 );
>
> ERROR at line 5:
> ORA-00936: missing expression
> UPDATE bridge
> SET bridge.contact_data = com_user_contacts.contact_data
> WHERE ( select com_user_contacts.contact_data
> from com_user_contacts, bridge
> where com_user_contacts.userid = bridge.old_userid );
>
>
>
> Sent via Deja.com
> http://www.deja.com/
>
Received on Sat Jan 27 2001 - 23:16:13 CST