Having problem with Update from query

[msalman]

Hey guys,

I'm trying to update some columns in a table using another table's data but there seems to be some problem due to join among the two tables. Here is my query

Update DMM_SalesReporting..sapis_test
		SET 	Claims_Qty = ISNULL(Claims_Qty, 0) 
			     + CONVERT(varchar(30), b.BaseUOMQuantity),
			Claims_Dollars = ISNULL(Claims_Dollars, 0) 
				 + b.BaseUOMAmount,
			Update_DateTime = getdate(), MM_Batch_Num = convert(varchar(30), b.MM_Batch_Num)		
		FROM DMM_SalesReporting..sapis_test a, Buckets_Staging b 
		WHERE 	b.bucket = 'Claims' and a.Month_Key = b.Month_key and 
			a.sku_wid = convert(varchar(10), b.FromSku_wid) and 
			a.Plant_wid = convert(varchar(10), b.FromPlant_wid) and a.PlantFlag = b.PlantFlag

Currently, the data that i'm working with, there are only 9 records in table Bucket_Staging, which are associated with Bucket = Claims. However, when i run this query it updates 33 records. I ran a SELECT query to check few things and realized that it is updating some records multiple times. So i was wondering if there's a way around this problem because i need to keep track of number of records failed and updated successfully.

Thanking in advance!

[code green]

there are only 9 records in table Bucket_Staging, However, when i run this query it updates 33 records

Impossible if there are only nine records!

I ran a SELECT query to check few things

What things?

and realized that it is updating some records multiple times.

How do you know this?

because i need to keep track of number of records failed and updated successfully

Understable, But how are you doing this?

[msalman]

Impossible if there are only nine records!

Please see the following screen-shot

How do you know this?

Please see the difference yourself

Understable, But how are you doing this?

I first count number of records in the table for each bucket and then use the value of @@ROWCOUNT to determine the number of failed records.

[ck9663]

Please see the following screen-shot



Please see the difference yourself






I first count number of records in the table for each bucket and then use the value of @@ROWCOUNT to determine the number of failed records.

you have duplicate records... you might want to clean your table first

-- ck

[code green]

you have duplicate records... you might want to clean your table first

Well done ck9663. Never thought of that!