Troublesome Update statement

Question

Post reply

Troublesome Update statement

eseosaoregie

SSCertifiable

Points: 6889
More actions
February 13, 2009 at 3:35 am

#73459

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105712 More actions · Answer 1

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

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 2

eseosaoregie

SSCertifiable

Points: 6889

February 13, 2009 at 4:43 am

#943444

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?

Ken McKelvey SSCoach Points: 18484 More actions · Answer 3

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.

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 4

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.

Ken McKelvey SSCoach Points: 18484 More actions · Answer 5

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



(

&nbsp&nbsp&nbsp&nbspproviderCode varchar(20)

&nbsp&nbsp&nbsp&nbsp,practiceCode varchar(20)

&nbsp&nbsp&nbsp&nbsp,POD varchar(20)

&nbsp&nbsp&nbsp&nbsp,activityMonth tinyint

&nbsp&nbsp&nbsp&nbsp,highCostRiskPool bit

&nbsp&nbsp&nbsp&nbsp,actualActivity int

&nbsp&nbsp&nbsp&nbsp,actualCost money

&nbsp&nbsp&nbsp&nbsp,SpecialtyCode varchar(20)

&nbsp&nbsp&nbsp&nbsp,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

(

&nbsp&nbsp&nbsp&nbspproviderCode varchar(20)

&nbsp&nbsp&nbsp&nbsp,practiceCode varchar(20)

&nbsp&nbsp&nbsp&nbsp,POD varchar(20)

&nbsp&nbsp&nbsp&nbsp,activityMonth tinyint

&nbsp&nbsp&nbsp&nbsp,highCostRiskPool bit

&nbsp&nbsp&nbsp&nbsp,Activity int

&nbsp&nbsp&nbsp&nbsp,Cost money

&nbsp&nbsp&nbsp&nbsp,SpecialtyCode varchar(20)

&nbsp&nbsp&nbsp&nbsp,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'

StarNamer SSCrazy Eights Points: 8633 More actions · Answer 6

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

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 7

eseosaoregie

SSCertifiable

Points: 6889

February 13, 2009 at 10:20 am

#943779

Thanks. That makes sense now.