Solved

Problem with UPDATE TABLE1 FROM TABLE2 in SQL Server 2005

Posted on 2014-03-12
2
659 Views
Last Modified: 2014-03-12
Running code on SQL Server 2005, and code is inserting rows from our tbl_extract table into our tbl_target table.

tbl_extract - sample data

GLMCU          GLOBJ_ObjectAccount   DomesticAmount

BBIF054978     5130                              1021
BBIF054978     5130                              15610

We can have multiple entries for each GLMCU value.

tbl_target is then populated so that we have an equivalent row for every row from tbl_extract (number of columns on tbl_target has been cut down for legibility)
but at that stage the Rep_Sales column is specifically set to 0

tbl_target - sample data

Tour           Rep_Sales              

BBIF054978     0
BBIF054978     0

Just as we can have multiple entries for each GLMCU value on tbl_extract, we can also have multiple entries for each Tour value on tbl_target.

Later on in processing, we try to set our Rep_Sales values with the following statement :

UPDATE tbl_target
SET Rep_Sales = f.DomesticAmount
FROM tbl_extract f  
WHERE
tbl_target.Tour = f.GLMCU
AND f.GLOBJ_ObjectAccount
IN
('5130', '5120', '5134')

After that statement our tbl_target contents are

Tour           Rep_Sales              

BBIF054978     1021
BBIF054978     1021

So the first entry for Tour BBIF054978 on tbl_target has a Rep_Sales value of 1021 and so is obviously correct.  But instead of the second entry for
Tour BBIF054978 on tbl_target having a Rep_Sales value of 15610 as required, it also ends up with a Rep_Sales value of 1021.

How can I code the update statement so that it picks up BOTH of the values for DomesticAmount from the tbl_extract table ?

Note that we are using SQL Server 2005.

Thanks in advance for any assistance here.
0
Comment
Question by:raymurphy
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39923411
I had written an article about UPDATE with JOIN:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

it will also explain what the problem is here: you don't seem to have a row-to-row joining condition. the value of BBIF054978      alone cannot help which row to update, so unless you have a second field to uniquely identify/order the rows, it will be "impossible" to solve this request.

please clarify
0
 

Author Comment

by:raymurphy
ID: 39923615
Thanks for the prompt reply, Guy, and for the link to your article which was very interesting and useful. Have now resolved this problem by adding an id column to both tables and the using that for the row-row joining condition, which now gives the required results ...
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

770 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