no

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 mult...

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

Related

database 2690689717176281212

Post a Comment

1 comment

maC blog said...

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

item