Skip to main content

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

Popular Posts

Ho Ching named 5th most powerful and is mistaken as first lady by Forbes

Forbes named Singapore Prime Minister wife and CEO of Temasek Holdings, Ho Ching, as the 5th most powerful woman in the world. Ho Ching is mistaken as Singapore's first lady! OMG!

I wonder how can Forbes makes such a blunder. For a complete list, refer to here.

How to stop FortiClient from starting automatically?

Installed FortiClient recently but the challenge in disabling the application/service from running automatically on every start-up annoyed me. Attempt to stop 'FortiClient Service Scheduler' only return 'Parameter is incorrect' error message.

An article on Technet help solve my trouble. To stop FortiClient from starting automatically, try the following:
Shut down FortiClient from the system tray.
Run net stop fortishield on command prompt.
Run msconfig.
On msconfig, switch to the Services tab. Clear the FortiClient Service Scheduler check box and click Apply.Run services.msc on command prompt to open up show all available services.Look for FortiClient Service Scheduler. Switch Startup type to Manual.Restart your computer. FortiClient should not be running automatically the next time round. Hope it helps.

ACJC Birthday Girl Tied Up and Tortured

The two videos mentioned are particularly shocking to me because of two factors. First of all, the perpetrators are practically all females, with a few boys in the background cheering. I never knew women are into such cruelty as well. Second, it occurred in bright daylight at a crowded school canteen - where everybody can see! The poor victim was tied to a chin-up bar and attacked mercilessly. All in the name of “celebrating” her birthday.

The school where all these took place is a well-reputed school - Anglo-Chinese Junior College (ACJC). (No point in concealing the school’s identity as the uniform of the students involved is obvious).

Video captured on the incident was uploaded onto YouTube but was taken down after this matter was blown up.

ACJC girl gets tied up and tortured… by friends “celebrating” her birthday [via]