Transact SQL Combine Customer Records

Can someone please helpme with a SQL Update Query?

Occasionally I get duplicate customer records because they have setup a second account with their work email and forgot that they had originally setup their account with their home email.

I want to write a query that will essentially combine the 2 records and then delete 1 of them.

So, for example, I want to take record id #202 and record id #561 and combine 3 fields: TotalSpent, TotalNumberof Orders, and logincount and write the totals into record id #561. Then, upon successful update, delete record id #202.

Any help will be appreciated.

David
DLockwoodAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DLockwoodConnect With a Mentor Author Commented:
I found the easy answer to this question somewhere else.

The easy answer is to set 4 local variables equal to the values in the table that I want to delete.

Then run a simple update query updating the values to the old value + the local variable. Then delete the old record.

Thanks for your help anyway.
0
 
Ephraim WangoyaCommented:
try this
;with 
cte1 as	
(
	select AccountNum, Email, Spent, Orders, logincount,
	  ROW_NUMBER() over (PARTITION by AccountNum order by ID desc) RN 
	from customer
),
cte2 as
	(
	select AccountNum, SUM(Spent) TotalSpent, SUM(Orders) TotalOrders, sum(logincount) logincount, max(Email) Email, COUNT(*) Num
	from customer
	group by AccountNum
	)

update cte1
set Spent = B.TotalSpent,
    Email = coalesce(Email, B.Email),
    Orders = B.TotalOrders,
    logincount = B.logincount
from cte2 B
inner join cte1 on cte1.AccountNum = B.AccountNum and B.Num > 1
where RN = 1

delete 
from cte1 
where rn > 1

Open in new window

0
 
DLockwoodAuthor Commented:
Found the easy answer myself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.