Update the all duplicated records in the database!

Question

Update the all duplicated records in the database!

ATroung

SSC Enthusiast

Points: 164
More actions
October 18, 2007 at 2:43 pm

#183024

Hello all,
I have a large data table with many duplicated ID records. A portion of my data table as following:
ID |saleDate | SaleAmount
==============================
40 |06/23/2003 | 242
41 |11/30/2001 | 1000
40 |12/28/2006 | 343
41 |10/25/1996 | 154
41 |04/27/2007 | 1234
42 |05/07/2006 | 777
I would like to update all duplicated records using SUM(SaleAmount) to replace for SaleAmount.
The data result will be
IDsaleDate SaleAmount
==============================
40 |06/23/2003 | 585 (242 + 342)
41 |11/30/2001 | 2388 (1000+1234+154)
40 |12/28/2006 | 585
41 |10/25/1996 | 2388
41 |04/27/2007 | 2388
42 |05/07/2006 | 777
Please help me with TSQL for update. Thanks in advance.

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

You DO realize that if you keep running this, your sales amount will keep skyrocketing (because you haven't fixed the duplication). This is a one-shot deal, unless you want those sales figures to start grow algebraically.

Now - I feel duty-bound to bring this up again. You're SURE this is what you want?

Fine - your call....

This is an UPDATE, so this will REPLACE your existing saleamount.

All right - you asked for it 🙂

update T

set T.saleamount=tot.Totsales

from salestable T inner join

(select t1.Id, sum(t1.saleamount) as Totsales from salestable T1 group by t1.id) tot

on t.id=tot.id

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

ATroung SSC Enthusiast Points: 164 More actions · Answer 2

Hi,

Thank for your help quickly to solve my question. In fact, I got a data table which need to be updated that way for moving cost. I got multi duplicated records that missing information in cost column. They would like to pull out only one of duplicated records into new table with replacing moving cost with the Sum(cost) for survey. Anyway, thank for showing me how to using a SQL JOIN in a SQL UPDATE statement.

ATroung SSC Enthusiast Points: 164 More actions · Answer 3

Hello Matt,

Back to your code that you helped me as following:

update T

set T.saleamount=tot.Totsales

from salestable T inner join

(select t1.Id, sum(t1.saleamount) as Totsales from salestable T1 group by t1.id) tot

on t.id=tot.id

I have some questions on above SQL code. My saleamount data type is nvarchar. Thus, SUM(t1.saleamount) will generate the error during execute the update query. How do I convert saleamount data type from nvarchar to int using CAST operation in above query? Your feedback is appreciated. Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Why are you storing amounts in a character field? The following will work, provided there are no values in the column that are not ints.

update T

set T.saleamount=tot.Totsales

from salestable T inner join

(select t1.Id, sum(CAST(t1.saleamount AS INT)) as Totsales from salestable T1 group by t1.id) tot

on t.id=tot.id

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nebojsa Ilic SSCommitted Points: 1735 More actions · Answer 5

In addition to Gail script I'll suggest to check whether a content is a valid numeric type. For example, you can add in inner query where clause (WHERE ISNUMERIC(SaleAmount)=1)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Just be aware that isnumeric can return true for values that can't be cast to int.

SELECT ISNUMERIC ('346E05'), ISNUMERIC ('25,2566')

SELECT CAST('346E05' AS int)

SELECT CAST('25,2566' AS int)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ATroung SSC Enthusiast Points: 164 More actions · Answer 7

Hi,

Thanks for your help. Actually, I was not a person to create the table structure. This table share with a lot of developers. So, I could not change the data type.