Problem with UPDATE TABLE1 FROM TABLE2 in SQL Server 2005
Posted on 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
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 :
SET Rep_Sales = f.DomesticAmount
FROM tbl_extract f
tbl_target.Tour = f.GLMCU
('5130', '5120', '5134')
After that statement our tbl_target contents are
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.