asked on

one to many update help needed

Hello, I'm attempting to use the following to update a table from another table. This is a one to many update.

When I run this only one row is updated which is not correct.

update a
set a.custnmbr = b.custnmbr
From BB_TNCommFac3_LocationsBScott a, cc_scott b
where ltrim(rtrim(b.oldid)) = ltrim(rtrim(a.oldid))

this brings 1149 rows
select * from cc_scott where ltrim(rtrim(oldid)) in (select ltrim(rtrim(oldid)) from BB_TNCommFac3_LocationsBScott)

ASKER CERTIFIED SOLUTION

mgrabarz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ahrensy

I would do something like:

update table 1 set table1.field = (select table2.field from table2 where table1.id = table2.id)

Open in new window

gogetsome

ASKER

Yes, I need to update the custnmbr on BB_TNCommFac3_LocationsBScott with the custnmbr on cc_scott based on the oldid that resides in both tables.

cc_scott is the billing table and BB_TNCommFac3_LocationsBScott is the shipping table. There are more shipping address then billing addresses. All shipping addresses need the same custnmbr as their corresponding billing custnmbr.

The cc_scott table has 1149 rows and the BB_TNCommFac3_LocationsBScott table has 1909 rows.

Dmapros

I typically use this format when updating items based on data from another table:

UPDATE <tblname>
SET <colname> =
SELECT <colname with data for update>

Dmapros

Sorry last post was not complete when it uploaded.

typically use this format when updating items based on data from another table:

UPDATE <tblname>
SET <colname> =
SELECT <colname with data for update>
FROM <tablename>
WHERE <conditions>