0

I have a DB-Table with:

ID    belongsTo   sort
100   null        null
221   100         null
345   100         null
432   100         null
587   100         null
627   100         null
787   100         null

I want to fill the sort-column with values 100, 200, 300,... for the lines with belongsTo=100 in ascending order of ID. Is this possible with one update statement? Is there a possibility to apply an ORDER BY to the update and use ROWNUM?

So I want the following result in this case:

ID    belongsTo   sort
100   null        null
221   100         100
345   100         200
432   100         300
587   100         400
627   100         500
787   100         600
anm
  • 545
  • 3
  • 17
  • Please give more info. When do you want to update it with 100? When with 200? When with 300? What is the desired output on this data? – Goran Kutlaca Aug 01 '18 at 08:49
  • 1
    I added the result which I want to get. The sort-column should be filled with values in ascending order (in steps of 100), in the order of the id-column. Only lines with the value "100" in belongsTo should be filled... – anm Aug 01 '18 at 08:55
  • Had you needed to update a chunk of ID's in sort = 100 and chunk in sort = 200 and so on, maybe it would have been more practical with CASE in single update statement. But you need to loop your update statement, because you need to constantly change your sort value and you can change it with counter sort_counter := sort_counter + 100; In LOOP you can order your statement as you wish. – Goran Kutlaca Aug 01 '18 at 09:06

0 Answers0