CODEWITHSUNDEEP
sql-serversql-server-2008sql-updateduplicatesduplicate-data
user3016727
user3016727

Reputation: 23

Updating of duplicate records

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?

Upvotes: 1

Views: 66

Answers (3)

code save
code save

Reputation: 1106

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.

Upvotes: 0

PrfctByDsgn
PrfctByDsgn

Reputation: 1050

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

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

;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;

Upvotes: 2

Related Questions