Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

one to many update help needed

Posted on 2010-09-21
5
Medium Priority
?
268 Views
Last Modified: 2012-05-10
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
Comment
Question by:gogetsome
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 2

Accepted Solution

by:
mgrabarz earned 2000 total points
ID: 33726083
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
 
LVL 2

Expert Comment

by:Ahrensy
ID: 33726093
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
 

Author Comment

by:gogetsome
ID: 33726141
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
 
LVL 1

Expert Comment

by:Dmapros
ID: 33726395
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
 
LVL 1

Expert Comment

by:Dmapros
ID: 33726417
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question