Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

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)




0
gogetsome
Asked:
gogetsome
1 Solution
 
mgrabarzCommented:
select * from cc_scott where ltrim(rtrim(oldid)) in (select ltrim(rtrim(oldid)) from BB_TNCommFac3_LocationsBScott)
This query returns rows from cc_scott  table that have the value of oldid existing in BB_TNCommFac3_LocationsBScott. Maybe there are 1149 rows with the same oldid value?

In update process you will update the same BB_TNCommFac3_LocationsBScott row for 1149 times...
0
 
AhrensyCommented:
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

0
 
gogetsomeAuthor Commented:
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.
0
 
DmaprosCommented:
I typically use this format when updating items based on data from another table:

UPDATE <tblname>
SET <colname> =
SELECT <colname with data for update>
0
 
DmaprosCommented:
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>

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now