Sql script to correct data

Question

I have the following data in a table:

Id      | InventoryId | RevisionId
-----------------------------------
1001    |   1234    |   1
1002    |   2235    |   1
1003    |   2235    |   2
1004    |   2235    |   2
1005    |   2235    |   3
1006    |   2235    |   4
1007    |   3234    |   1
1008    |   3234    |   2
1009    |   3234    |   3
1010    |   3234    |   3
1011    |   3234    |   4
1012    |   3234    |   5
1013    |   3234    |   5
1014    |   3234    |   6

I want to write a script which provide me data correction in following way

Id   |  InventoryId | RevisionId
---------------------------------
1001    |   1234    |   1
1002    |   2235    |   1
1003    |   2235    |   2
1004    |   2235    |   3
1005    |   2235    |   4
1006    |   2235    |   5
1007    |   3234    |   1
1008    |   3234    |   2
1009    |   3234    |   3
1010    |   3234    |   4
1011    |   3234    |   5
1012    |   3234    |   6
1013    |   3234    |   7
1014    |   3234    |   8

Can we do this with a SQL script?

Any suggestions?

score 4 · Answer 1 · answered Mar 28 '19 at 05:32

4

use row_number()

select *, row_number() over(partition by InventoryId order by id) as newRevisionId
from tablename

answered Mar 28 '19 at 05:32

Fahmi

37,315
5
22
31

1

exact same answer I was about to post and saw this. – Ajan Balakumaran Mar 28 '19 at 05:39
1

@AjanBalakumaran, you can still post it just add some explanation so that it becomes unique :) – Fahmi Mar 28 '19 at 05:40
2

I'm upvoting this on the grounds (perhaps not intended by the author) that the OP might not even want to do an update, but rather just generate this sequence when he needs it. – Tim Biegeleisen Mar 28 '19 at 05:43

score 2 · Answer 2 · answered Mar 28 '19 at 05:39

Try this:

    DECLARE @Table TABLE(Id INT,InventoryId INT,RevisionId INT)

    INSERT INTO @Table
    SELECT 1001,1234,1 UNION ALL
    SELECT 1002,2235,1 UNION ALL
    SELECT 1003,2235,2 UNION ALL
    SELECT 1004,2235,2 UNION ALL
    SELECT 1005,2235,3 UNION ALL
    SELECT 1006,2235,4 UNION ALL
    SELECT 1007,3234,1 UNION ALL
    SELECT 1008,3234,2 UNION ALL
    SELECT 1009,3234,3 UNION ALL
    SELECT 1010,3234,3 UNION ALL
    SELECT 1011,3234,4 UNION ALL
    SELECT 1012,3234,5 UNION ALL
    SELECT 1013,3234,5 UNION ALL
    SELECT 1014,3234,6

    ;WITH cte
    AS(
        SELECT Id,InventoryId,RevisionId, ROW_NUMBER() OVER(PARTITION BY InventoryId ORDER BY id) AS NewRevision FROM @Table
      )
    UPDATE cte SET RevisionId=newRevision

    SELECT * FROM @Table

can we update only those record in which revisionId is duplicate instead of updating all the revisionId — Ankit Vaidya, Mar 28 '19 at 05:53
include "where RevisionId<>newRevision" as where clause in update statement — Sahi, Mar 28 '19 at 07:04

PSK · Answer 3 · 2019-03-28T05:45:21.623

2

If you want to update the values back to the table with new RevisionId, you can use CTE like following.

;with cte as
(
 select *, row_number() over(partition by InventoryId order by id) as rn
 from @table 
)

 update cte set RevisionId = rn

edited Mar 28 '19 at 05:45

answered Mar 28 '19 at 05:39

PSK

17,547
5
32
43

score 0 · Answer 4 · answered Mar 28 '19 at 13:41

create table #temp1(A int,B int)

insert into #temp1(a,b)values (1001,1234) ,(1002,2235) ,(1003,2235) ,(1004,2235) ,(1005,2235) ,(1006,2235) ,(1007,3234) ,(1008,3234) ,(1009,3234) ,(1010,3234) ,(1011,3234) ,(1012,3234) ,(1013,3234) ,(1014,3234)

select *,row_number() over(partition by b order by b asc) from #temp1

Sql script to correct data

4 Answers4