Updating of duplicate records

Question

I want to update the postal code for the same customers based on their latest purchase date.

Customer_code      Postal_code   last_purchase_date
12345              444555        20130131
12345              444555        20130131
12345              123456        20110131

As the third field postal code is outdated, i want to update it and replace it with the latest postal code which is '444555' based on the latest purchase date. because i have a few hundred thousand fields similar to this, any suggestions for this?

score 2 · Accepted Answer · answered Nov 21 '13 at 09:08

2

;WITH x AS 
(
  SELECT Customer_code, Postal_code, rn = ROW_NUMBER() OVER 
    (PARTITION BY Customer_code ORDER BY last_purchase_date DESC)
  FROM dbo.some_table_name
)
UPDATE x SET x.Postal_code = y.Postal_code
  FROM x INNER JOIN x AS y
  ON x.Customer_code = y.Customer_code
  WHERE y.rn = 1 AND x.rn > 1
  AND COALESCE(x.Postal_code, '') <> y.Postal_code;

answered Nov 21 '13 at 09:08

Aaron Bertrand

272,866
37
466
490

Really appreciate your help Aaron!! Thank you so much! The query worked perfectly :) – user3016727 Nov 22 '13 at 07:29

score 0 · Answer 2 · answered Nov 21 '13 at 09:19

0

another possible solution without CTE:

update tab 
    set postal_code = 
        (select top 1 postal_code from tab x where x.customer_code = t.customer_code order by last_purchase_date desc)
    from tab t

answered Nov 21 '13 at 09:19

PrfctByDsgn

1,022
1
14
18

What's wrong with a CTE? It's a very repeatable pattern for many similar problems, not all of them solvable by a top 1 correlated subquery. – Aaron Bertrand Nov 21 '13 at 09:41
nothing's wrong with CTEs ... I just wanted to show an alternative ... maybe someone has a similar problem with a dbms that doesn't support CTEs ... they are Microsoft specific, aren't they? – PrfctByDsgn Nov 21 '13 at 09:55
Well, the question was tagged [tag:sql-server], so unless there is a requirement to provide a database-agnostic query (which, for anything even remotely complex, isn't possible anyway), I don't see the problem with proprietary answers - especially when, as I said, they present reusable and portable patterns. – Aaron Bertrand Nov 21 '13 at 09:57
Really appreciate your help! @AaronBertrand Thank you so much! The query worked perfectly :) – user3016727 Nov 22 '13 at 06:39

score 0 · Answer 3 · answered Nov 21 '13 at 12:07

0

Please Try it

WITH cte AS 
(
  SELECT *, rn = ROW_NUMBER() OVER 
    (PARTITION BY Postal_code ORDER BY Customer_code )
  FROM dbo.some_table_name
)

update cte set last_purchase_date='' where rn = 2

Update according your recuirnment.

answered Nov 21 '13 at 12:07

code save

1,054
1
9
15

Updating of duplicate records

3 Answers3