Friday, March 22, 2013

MSSQL: Update running sequence / number

Let's say we have a table with a column of numbers and for some reason, they are not in running sequence. Instead of editing the rows manually, we can actually do it using a SQL script.

Hopefully the following helps.

--Create some test data
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'tblTest') AND type in (N'U')) DROP TABLE tblTest
GO
CREATE TABLE tblTest (Label VARCHAR(20), OriginalOrderNo INT, OrderNo INT)
INSERT INTO tblTest (Label, OriginalOrderNo, OrderNo) VALUES ('four', 4, 4)
INSERT INTO tblTest (Label, OriginalOrderNo, OrderNo) VALUES ('seven', 7, 7)
INSERT INTO tblTest (Label, OriginalOrderNo, OrderNo) VALUES ('one', 1, 1)
INSERT INTO tblTest (Label, OriginalOrderNo, OrderNo) VALUES ('two', 2, 2)
INSERT INTO tblTest (Label, OriginalOrderNo, OrderNo) VALUES ('five', 5, 5)
INSERT INTO tblTest (Label, OriginalOrderNo, OrderNo) VALUES ('nine', 9, 9)
GO
--BEFORE
SELECT * FROM tblTest ORDER BY OrderNo
GO
--DO the UPDATE
UPDATE t1 SET t1.OrderNo = t2.Sequence
FROM tblTest t1 INNER JOIN (
 SELECT Label, OrderNo, ROW_NUMBER() OVER (ORDER BY OrderNo) AS 'Sequence'
 FROM tblTest) AS t2 ON t1.OrderNo = t2.OrderNo
GO
--AFTER
SELECT * FROM tblTest ORDER BY OrderNo
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'tblTest') AND type in (N'U')) DROP TABLE tblTest
GO

1 comment:

  1. Choose your favorite image from your device gallery or capture the real picture with your smartphone camera to create a drawing photo sketch & pencil sketch photo editor & set some stylish color drawing effect and sparkle effect. When you make a beautiful pencil sketch of your image then you feel it’s the best photo sketch - convert photo to sketch app and sparkle effect in the world.

    ReplyDelete

Do provide your constructive comment. I appreciate that.