How to Add Order to an existing table?

Question

I have table called Products. Let say this is my table,

ID    Name     ParentID
--    ---       --------
1      a        NULL
2      b        NULL
3      a1       1
4      a2       1
5      b2       2
6      b2       2

Now I need to add [Order] Column with respect to ParentID,

ID    Name     ParentID    Order
--    ---       --------   ----
1      a        NULL        NULL
2      b        NULL        NULL
3      a1       1            1
4      a2       1            2   
5      b2       2            1  
6      b2       2            2

Creating [Order] is trivial but inserting record is a bit tricky part

Well you are here long enough to know the rules. So show us your work please. — K_B, Dec 19 '13 at 09:09
Do we really need to blame somebody who hasn't ever used `ROW_NUMBER`? What would somebody try if he didn't know about this function? — Smileek, Dec 19 '13 at 09:17
@Smileek Cursors come to mind. I believe that every question, regardless of the asker's ability or difficulty of the question, needs to show an attempt at solving the problem oneself, even if it's just sharing a few thoughts about what won't work. — Bernhard Barker, Dec 19 '13 at 23:41

bjnr · Accepted Answer · 2014-04-25T05:16:20.347

3

UPDATE  [Products]
SET  [Products].[Order] = PTT.[Order]
FROM
    [Products]
    INNER JOIN (SELECT ID, ROW_NUMBER() OVER (PARTITION BY PT.ParentID ORDER BY ID) AS [Order]
                FROM [Products] PT
                WHERE PT.ParentID IS NOT NULL) AS PTT ON PTT.ID = [Products].ID

edited Apr 25 '14 at 05:16

answered Dec 19 '13 at 09:13

bjnr

3,213
16
30

1

Thank you Thank you. Just update your anser to, UPDATE [Products] SET [Products].[Order] = PTT.[Order] FROM [Products] INNER JOIN (SELECT ID, ROW_NUMBER() OVER (PARTITION BY PT.ParentID ORDER BY Name) AS [Order] FROM [Products] PT WHERE PT.ParentID IS NOT NULL) AS PTT ON PTT.ID = [Products].ID – Imran Qadir Baksh - Baloch Dec 19 '13 at 09:37
1

[You don't need a self join here](http://stackoverflow.com/questions/3439110/sql-server-update-a-table-by-using-order-by/3439180#3439180) – Martin Smith Dec 19 '13 at 11:09
@user960567 - It's SQL Hint's answer. I'll leave it up to them if they want to remove the unnecessary join. – Martin Smith Dec 19 '13 at 12:07
@MartinSmith, OK Thanks – Imran Qadir Baksh - Baloch Dec 19 '13 at 12:08

How to Add Order to an existing table?

1 Answers1