I want to give serial numbers for particular IDs in existing colomns

I have Products table and 5000 records are there, and I need to update serial numbers for 2000 records.

old table

Id  Name   Price  Recommended
45  Lotus  450    500
55  Cherry 560    500
56  Berry  789    566

new table

Id  Name   Price  Recommended
45  Lotus  450    1
55  Cherry 560    2
56  Berry  789    3

Answers


You can't, unfortunately, use a window function directly in the set clause. You could, however, use it in a subquery, and then join that query on your table when updating:

UPDATE p
SET    p.recommended = rn
FROM   products p
JOIN   (SELECT id, ROW_NUMBER() OVER (ORDER BY recommended) AS rn
        FROM   products) r ON p.id = r.id

SQLFiddle


select row_number() over ( order by (select null)), *
from OldTable

you can try with cte

   ;WITH cte AS
    (   
        SELECT  
               id,
               ROW_NUMBER() OVER (ORDER BY recommended) AS rn, 
               name,
               price

        FROM   Products
    )
    UPDATE  cte
    SET recommended = rn
    -- if you have any condition put here for example ( where rn <= 2000)

Need Your Help

Qml Video creation out of image stream from C++

c++ image video-streaming qml qt5

After 10 days of endeavor, I'm back to the point of asking a fundamental question on my task.

HTML editor Cocoa control

html cocoa editor controls

I am looking for a Cocoa HTML editor control.