By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,442 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,442 IT Pros & Developers. It's quick & easy.

Having problem with Update from query

P: 6
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
Expand|Select|Wrap|Line Numbers
  1. Update DMM_SalesReporting..sapis_test
  2.         SET     Claims_Qty = ISNULL(Claims_Qty, 0) 
  3.                  + CONVERT(varchar(30), b.BaseUOMQuantity),
  4.             Claims_Dollars = ISNULL(Claims_Dollars, 0) 
  5.                  + b.BaseUOMAmount,
  6.             Update_DateTime = getdate(), MM_Batch_Num = convert(varchar(30), b.MM_Batch_Num)        
  7.         FROM DMM_SalesReporting..sapis_test a, Buckets_Staging b 
  8.         WHERE     b.bucket = 'Claims' and a.Month_Key = b.Month_key and 
  9.             a.sku_wid = convert(varchar(10), b.FromSku_wid) and 
  10.             a.Plant_wid = convert(varchar(10), b.FromPlant_wid) and a.PlantFlag = b.PlantFlag
  11.  
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!
Feb 4 '08 #1
Share this Question
Share on Google+
4 Replies


code green
Expert 100+
P: 1,726
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?
Feb 4 '08 #2

P: 6
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.
Feb 4 '08 #3

ck9663
Expert 2.5K+
P: 2,878
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
Feb 4 '08 #4

code green
Expert 100+
P: 1,726
you have duplicate records... you might want to clean your table first
Well done ck9663. Never thought of that!
Feb 5 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.