raymurphy
asked on
Problem with UPDATE TABLE1 FROM TABLE2 in SQL Server 2005
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER