4

After reading this question, I have another similar question. Is there a simple way to get this SQL statement to work?

update [Insurances] set [RowNo]=ROW_NUMBER() over (order by [RowNo])

I have a RowNo column which I want to update whenever a record is deleted.

Community
  • 1
  • 1
iMan Biglari
  • 4,674
  • 1
  • 38
  • 83
  • You really should do this in a view instead of in the actual table... – JNK Sep 12 '12 at 14:37
  • @JNK Could you be a little more specific? I'm pretty new to the SQL world – iMan Biglari Sep 12 '12 at 14:39
  • If all you want to know is what order the row is within a certain ordering logic, it shouldn't be a part of the actual row since it's metadata. Create a `ROW_NUMBER()` field in a view and just use that. – JNK Sep 12 '12 at 14:41
  • @JNK Actually I change that `RowNo` column to modify each row's position in my resultset. – iMan Biglari Sep 12 '12 at 14:42
  • Then it is indeed a terrible idea. Just use a view instead. – JNK Sep 12 '12 at 14:45
  • do you keep the `RowNo` when you delete a record(Single Row I assume) ? a simple/quick thought instead using `RowNumber()` to resequence would `update Insurances SET RowNo = RowNo - 1 Where RowNo >= DeletedRowNo` works ? – Turbot Sep 12 '12 at 14:46
  • @Turbot Unfortunately I expect multiple row deletions to occur sometimes. – iMan Biglari Sep 12 '12 at 14:48
  • 1
    Why do you need to store this. Ever. You can always generate the number at runtime and it is guaranteed to be up to date without having to constantly scan the entire table with a trigger every time a row is added, updated or deleted. – Aaron Bertrand Sep 12 '12 at 14:49
  • @AaronBertrand I need it because I need to let my users move the records up and down at their discretion. – iMan Biglari Sep 12 '12 at 14:54
  • Sorry, still don't understand why you can't just change the RowNo value instead of recalculating it for the whole table. – Aaron Bertrand Sep 12 '12 at 14:56
  • @iManBiglari - you don't need to update the `RowNo` since it is internally assigned and not visible to user. so either i don't understand why you need to update the whole table with the row_number(). – Turbot Sep 12 '12 at 15:53
  • 3
    There are some legitimate needs for doing this operation. – Jeff S May 10 '13 at 22:15
  • possible duplicate of [SQL Update with row\_number()](http://stackoverflow.com/questions/13648898/sql-update-with-row-number) – Stefan Steinegger Oct 31 '13 at 22:02

3 Answers3

9
UPDATE t1 
SET t1.RowNo = s.RowNo  
FROM  [Insurances] t1
INNER JOIN
(
     SELECT Id, ROW_NUMBER() OVER( ORDER BY Somefield DESC) RowNo
     FROM Insurances
) s ON t1.Id = s.Id
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • 1
    I couldn't get this to work, until I move the line begins with `SET` to right after `UPDATE t1` at the top – Henry Feb 07 '14 at 21:40
2

I would join the table to a subquery (not sure if you can do that in SQL Server) or create a view and join the table to that in the update:

CREATE VIEW InsurancesView AS
SELECT insuranceID, ROW_NUMBER() OVER ( ORDER BY RowNo ) AS newRowNo
  FROM Insurances;

(assuming you have a primary key named insuranceID)

UPDATE Insurances AS i
 INNER JOIN InsurancesView AS iv
    ON i.insuranceID = iv.insuranceID
   SET i.RowNo = iv.newRowNo;

Hope this helps.

David Faber
  • 12,277
  • 2
  • 29
  • 40
2
WITH cte AS
(
SELECT Id, ROW_NUMBER() OVER(ORDER BY Name ) RowNo FROM lu_Domain
)
UPDATE dbo.lu_Domain
SET SortOrder = cte.RowNo
FROM dbo.lu_Domain d
INNER JOIN cte ON cte.id = d.Id
Rick Nash
  • 21
  • 3