Looking for a JavaEE Architect or Potential Tech Co-Founder?
Please don't hesitate to contact me.

MSSQL how to update a column base on a row number

Just recently I was asked to normalized or divide a table into 2 tables. Unfortunately, the only column that linked them (email) allows multiple same values, even aggregate would not do (email, password, etc). This is because there's a field that marks a user as inactive. So a single email can have many inactive records.

So linking the 2 tables, I only have 1 choice to update the other table based on row number, since the first table is filled with auto number.

How did I update a column base on a row number? See:

UPDATE Users
SET MainTableId=t.Seq
FROM (SELECT id, Row_Number() OVER (ORDER BY id) as Seq FROM Users) t
INNER JOIN Users ON t.id=Users.id

--test the table if properly updated
select * from Users
MSSQL how to update a column base on a row number MSSQL how to  update a column base on a row number Reviewed by Edward Legaspi on Tuesday, January 25, 2011 Rating: 5

1 comment:

maC blog said...

Good piece of code. I used it with pleasure! Thanks!!

Powered by Blogger.