Using Cursors i have to update the table

Question

I need help!

I have a table like this:

itemcode itemiicode itemordercode serialnum cpserialnum
4         2           4           21         21
5         2           5           -1         -1
6         3           6           14         14
7         4           7           15         15
8         5           8           41         41
9         6           9           72         72
10        6           10          -1         -1
11        6           11          -1         -1

I want a result table like below using Cursors

itemcode itemiicode itemordercode serialnum cpserialnum
4         2            4          21         21
5         2            5          -1         21
6         3            6          14         14
7         4            7          15         15
8         5            8          41         41
9         6            9          72         72
10        6            10         -1         72
11        6            11         -1         72

itemcode is the primary key Using Cursors we have to update the table

Please Help! Thanks and Regards Harsha

Answer 1

As I see, you want to update the column: cpserialnum.

The rule is: if the current value is -1 then the previous value should be inserted.

For this, you don't need to use CURSOR, but a simple inner select can make it, like this:

UPDATE items
SET cpserialnum =
(SELECT TOP 1 cpserialnum 
 FROM items innerItems
 WHERE innerItems.itemordercode <= items.itemordercode
 AND cpserialnum != -1
ORDER BY itemordercode DESC)
 FROM items

SELECT * FROM items

Here is an SQLFiddle demo.