Updating Product Order is not working

Question

I have Products table with (Name, ParentID, Order) columns. I have a insert statement which keep the inserted child products. After the insert I need to update the order,

I have the following SQL,

UPDATE  Products
SET     [Order]  = (SELECT ISNULL(MAX([Order]), 0) + 1 FROM Products WHERE ParentID = CP.ParentID) 
FROM    Products P
        INNER JOIN #InsertedChildProduct CP ON (CP.ID = P.ID)

The problem is that I am updating the order of products that are just inserted, but [Order] is not working. If I have,

Products
--------
ParentID  Order
----------------
1          1
1          2

and let say I have inserted 2 child products then the table should be,

Products
--------
ParentID  Order
----------------
1          1
1          2  
1          3
1          4

But I am seeing,

Products
--------
ParentID  Order
----------------
1          1
1          2  
1          3
1          3

this is simply executing the update on one pass, so MAX + 1 will be the same no matter how many rows you are updating — Tanner, Jul 01 '14 at 10:50
the answers on this question might help: http://stackoverflow.com/questions/1167885/update-sql-with-consecutive-numbering — Tanner, Jul 01 '14 at 10:55

Rahul · Answer 1 · 2014-07-01T11:31:33.397

0

Try like this instead, you need to gt the MAX() first in inner query

UPDATE  P
SET     [Order]  = X.newval
FROM Products P
JOIN 
(
SELECT ID, (ISNULL(MAX([Order]), 0) + 1) as newval 
FROM Products P
JOIN #InsertedChildProduct ip
on P.ParentID = ip.ParentID
group by ID  
) X  
ON X.ID = P.ID

edited Jul 01 '14 at 11:31

answered Jul 01 '14 at 10:54

Rahul

76,197
13
71
125

I am getting `An aggregate may not appear in the set list of an UPDATE statement.` – Imran Qadir Baksh - Baloch Jul 01 '14 at 11:14

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

You can try this, taken from the answers on here:

declare @MaxNumber int
set @MaxNumber = 0 
UPDATE  Products
SET [Order]  = @MaxNumber, @MaxNumber = (SELECT ISNULL(MAX([Order]), 0) 
                                         FROM Products 
                                         WHERE ParentID = CP.ParentID) + 1
FROM    Products P
        INNER JOIN #InsertedChildProduct CP ON (CP.ID = P.ID)

edited May 23 '17 at 10:26

Community

1
1

answered Jul 01 '14 at 11:09

Tanner

22,205
9
65
83

The problem is that `#InsertedChildProduct` include different parents. – Imran Qadir Baksh - Baloch Jul 01 '14 at 11:15
@user960567 so is your where clause and join not accounting for that? – Tanner Jul 01 '14 at 11:17
the current sql is the simplified version – Imran Qadir Baksh - Baloch Jul 01 '14 at 11:18
Is @MaxNumber set first or [Order] – Imran Qadir Baksh - Baloch Jul 01 '14 at 11:19
@MaxNumber should increment per insert according to the linked post, I've not tested it myself – Tanner Jul 01 '14 at 11:20
Tanner I am still getting same order for same parent, means same result – Imran Qadir Baksh - Baloch Jul 01 '14 at 11:21

VBlades · Answer 3 · 2014-07-01T12:35:54.163

Thought about this a bunch of different ways and can't see how this would work without imposing order on both datasets, even if arbitrary. Here's one way to do it (Fiddle - make sure to build the schema first, then run the code): http://www.sqlfiddle.com/#!3/d34df/3)

WITH cteRN_c
AS
(
  SELECT  ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS RN_c,
          ID
  FROM    #InsertedChildProduct
),
cteRN_p
AS
(
  SELECT  ROW_NUMBER() OVER (PARTITION BY ParentID ORDER BY [Order]) AS RN_p,
          ParentID,
          [Order]
  FROM    Products
  WHERE   [Order] IS NULL
)

UPDATE    p
SET       [Order] = (SELECT ISNULL(MAX([ORDER]), 0) FROM Products WHERE ParentID = p.ParentID) + c.RN_c                 
FROM      cteRN_p p INNER JOIN cteRN_c c
          ON p.ParentID = c.ID AND
             p.RN_p = c.RN_c;

We impose order by adding arbitrary row numbers to both the temp table set and the parent set, via ROW_NUMBER in CTEs. From that point, it's just a matter of joining the CTEs on the correct datapoints, and running the updates against the parent CTE. Granted, it's arbitrary which child will get numbered in which order, but at least it will happen.

Edit: Forgot the ISNULL in the MAX portion of the query - in case no children yet. Fiddle updated as well.

Updating Product Order is not working

3 Answers3