CODEWITHSUNDEEP
sql-server
jreloz
jreloz

Reputation: 430

Update a multiple records with duplicate column value

I have a query that identify how many times a ChassisNo was use:

Query:

SELECT 
    ROW_NUMBER() OVER (
        PARTITION BY ChassisNo
        ORDER BY datecreated ASC
    ) row_num,
    CollateralType,
    LoanID,
    ClientID,
    CollateralID,
    PlateNo,
    ChassisNo,
    EngineNo,
    datecreated,
    PreparedBy
FROM 
    TestAllLoanWithCollaterals

Result:

enter image description here

I highlighted an example of duplicated chassisno three times, some of the chassisno are duplicated 5 times or so, but the main thing is, how can I update all records with the same details with the latest chassisno

Expected result based on the highlighted example above:

enter image description here

The yellow highlight is the latest record based on the datecreated column and always the last row_num of each chassisno. the blue highlight is the columns that should be updated.

I am thinking of using the Database Cursor but I don't think it is possible.

Upvotes: 1

Views: 37

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

You may use an update join involving your original table and the logic you have already defined:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ChassisNo ORDER BY datecreated DESC) rn
    FROM TestAllLoanWithCollaterals
)

UPDATE a
SET
    CollateralType = b.CollateralType
    LoanID = b.LoanID
    ClientID = b.ClientID
    CollateralID = b.CollateralID
    PlateNo = b.PlateNo
    EngineNo = b.EngineNo
    datecreated = b.datecreated
    PreparedBy = b.PreparedBy
FROM TestAllLoanWithCollaterals a
INNER JOIN cte b
    ON a.ChassisNo = b.ChassisNo
WHERE
    b.rn = 1;

Note that the above update logic simply overwrites all fields among duplicate by chassis to use those of the record which were most recently updated in the group.

Upvotes: 1

Related Questions