February 13, 2009 at 3:35 am
I currently have a table where I would like to update various fields based on certain criteria. I update the records using an inner join in the statement to determine the relevant records to update. The inner join produces a dataset with 98000 or so records. Consequently,logically I would expect 98000 records to be updated. However only 23000 records are updated. Using the same logic I have tried to use a where statement instead of the join yet only 23000 are updated. Below is the sql I am using.
UPDATE zzPBC_Budget_v_Actuals
SET
actualActivity = A.activity,
actualCost = A.cost,
SpecialtyCode = A.specialty_Code,
HRG35_Code = A.HRG_Code
FROM
zzPBC_Budget_v_Actuals BvA INNER JOIN vw_zzPBC_Report_Actuals A ON
BvA.providerCode = A.providerCode AND
BvA.practiceCode = A.practiceCode AND
BvA.POD = A.POD AND
BvA.activityMonth = A.activityMonth AND
BvA.highCostRiskPool = A.highCostRiskPool
I have attached a sample of the output so that you can see what the recordset. Effectivetly for every row in the FROM clause recordset. I want to update the SpecialtyCode and HRG35_Code from the zzPBC_Budget_v_Actuals with the values from the specialty_Code and HRG_Code fields respectively. I was thinking of resorting to a cursor but I know cursor is can be resource intensive. Any ideas of where I am going wrong or anothermethod I could use would be much appreciated.
February 13, 2009 at 4:18 am
You're not updating the results of you join statement, but you're updating one of the tables. Maybe your join statement doesn't match exactly one record from the first table with one record from the second table.
Try running this:
SELECT *
FROM zzPBC_Budget_v_Actuals BvA
WHERE EXISTS (
SELECT 1
FROM vw_zzPBC_Report_Actuals A
WHERE
BvA.providerCode = A.providerCode AND
BvA.practiceCode = A.practiceCode AND
BvA.POD = A.POD AND
BvA.activityMonth = A.activityMonth AND
BvA.highCostRiskPool = A.highCostRiskPool
)
You should get 23000 records returned.
Regards
Gianluca
-- Gianluca Sartori
February 13, 2009 at 4:43 am
I have tried that script and I get 23000 records. The tables are such that there is not a 1 to 1 relationship where the join matches. If I get 98000 rows from the join, how can I update the those 98000 rows from the table i.e those instances where there are matches?
February 13, 2009 at 5:21 am
This is a classic example of why you should not use TSQL type UPDATEs unless you know
what you are doing. (We insist on ANSI UPDATEs in production code unless a good reason
for a TSQL UPDATE can be provided and the code is reviewed by someone who understands the
'bear traps'.)
You are updating rows in zzPBC_Budget_v_Actuals multiple times from rows in
vw_zzPBC_Report_Actuals with only the final update showing in zzPBC_Budget_v_Actuals.
Even worse, if you run the update multiple times with the same data you may not get the
same results as the order of the updates is not guareenteed. (A table is an unordered set.)
This can produce 'random' bugs in production systems.
You need to design a consistant logical basis for rows in zzPBC_Budget_v_Actuals to be
updated. You only want to update 23000 rows.
February 13, 2009 at 6:49 am
I understand what you are saying. However the 98000 records produced from the join is the correct number of records to be updated. The finance team have a separate system of doing there calculations and have come up with the same figure. The reason for this is because when you add up the activity and costs figure they match the figures given by my finance team. Clearly my logic is incorrect I just need some insight into how I can rectify this. I apologise for being a pain.
February 13, 2009 at 7:17 am
On the information you have given us so far 23000 is the correct number of rows to be updated.
Plug your query into the following test data, look at the results and think about it.
You need to decide what values go into actualActivity, actualCost, SpecialtyCode, HRG35_Code
Maybe SUM(Activity), SUM(Cost), MAX(SpecialtyCode), MAX(HRG35_Code)
Maybe SpecialtyCode and HRG35_Code already exist in zzPBC_Budget_v_Actuals, in which
case you may want to join on them as well and only update actualActivity and actualCost.
etc.
CREATE TABLE #zzPBC_Budget_v_Actuals
(
    providerCode varchar(20)
    ,practiceCode varchar(20)
    ,POD varchar(20)
    ,activityMonth tinyint
    ,highCostRiskPool bit
    ,actualActivity int
    ,actualCost money
    ,SpecialtyCode varchar(20)
    ,HRG35_Code varchar(20)
)
INSERT INTO #zzPBC_Budget_v_Actuals
SELECT 'A', 'A', 'Pea', 2, 1, NULL, NULL, NULL, NULL
CREATE TABLE #vw_zzPBC_Report_Actuals
(
    providerCode varchar(20)
    ,practiceCode varchar(20)
    ,POD varchar(20)
    ,activityMonth tinyint
    ,highCostRiskPool bit
    ,Activity int
    ,Cost money
    ,SpecialtyCode varchar(20)
    ,HRG35_Code varchar(20)
)
INSERT INTO #vw_zzPBC_Report_Actuals
SELECT 'A', 'A', 'Pea', 2, 1, 3, 200, 'A', 'B' UNION ALL
SELECT 'A', 'A', 'Pea', 2, 1, 1, 50, 'C', 'D' UNION ALL
SELECT 'A', 'A', 'Pea', 2, 1, 6, 100, 'E', 'F'
February 13, 2009 at 8:57 am
eseosaoregie (2/13/2009)
The inner join produces a dataset with 98000 or so records. Consequently,logically I would expect 98000 records to be updated. However only 23000 records are updated.
This is the flaw in your argument. Your join produces 98000 records by there are only 23000 distinct values from the left (BvA) side of the table, hence only 23000 records get updated.
Try the followingcreate table #a (n int, k int)
create table #b (n int, j int, k int)
insert #a
select 1,0 union all
select 2,0 union all
select 3,0 union all
select 4,0
insert #b
select 1,1,1 union all
select 1,2,2 union all
select 2,1,1 union all
select 2,2,2
select *
from
#a a join #b b
on a.n = b.n
update #a
set k=b.k
from
#a a join #b b
on a.n = b.n
drop table #a
drop table #b
If you look at the messages, you'll find that the select returns 4 rows from the join, but the update only updates 2 rows.
As mentioned elsewhere, it's actually updating each row multiple times.
Hopefully, this example is small enough to see that there could never be more than 2 rows updated even though the join produces 4 rows.
Derek
February 13, 2009 at 10:20 am
Thanks. That makes sense now.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply