Updating a table based on values in the table

Question

I have a temp table #XX that looks like this"

ID KWID NAME ORDER
1447 1144 SW_6368_58_55.png NULL
1450 1145 SW_6942_58_55.png NULL
1451 1147 Arb3_43_55.jpg NULL
1452 1147 Arb1_43_55.jpg NULL
1453 1147 Arb2_43_55.jpg NULL
1598 1152 A3_43_55.jpg NULL

The data is in order based on the KWID column, I want to update the ORDER column so that it has an incremented value... if there is (1) KWID, the value would be 1, if there are (2) entries the first one would be 1, the second 2 etc.

so the result would look like this:

ID KWID NAME ORDER
1447 1144 SW_6368_58_55.png 1
1450 1145 SW_6942_58_55.png 1
1451 1147 Arb3_43_55.jpg 1
1452 1147 Arb1_43_55.jpg 2
1453 1147 Arb2_43_55.jpg 3
1598 1152 A3_43_55.jpg 1

How can I accomplish this?

Answer

Hi @Jim Seidel , please find the working example below:

CREATE TABLE TEST(  
    ID INTEGER,  
    KWID INTEGER ,  
    NAME VARCHAR (50) NOT NULL,  
    ORDERS INTEGER  
);  

INSERT INTO TEST  
    (ID, KWID, NAME)  
 VALUES  
   ( 1447,1144 , 'SW_6368_58_51.png'),  
   ( 1450,1145 , 'SW_6368_58_52.png'),  
   ( 1451,1147 , 'SW_6368_58_53.png'),  
   ( 1452,1147 , 'SW_6368_58_54.png'),  
   ( 1453,1147 , 'SW_6368_58_55.png'),  
   ( 1598,1152 , 'SW_6368_58_56.png');  

UPDATE TEST t  
    SET orders = order2  
    FROM (  
        SELECT id, ROW_NUMBER() OVER (PARTITION BY kwid ORDER BY id) AS order2  
        FROM test  
        ) s  
    WHERE t.id = s.id;  

SELECT * FROM TEST;

Please try and let me know if this works for you or you still face any issues.

----------

If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.

Updating a table based on values in the table

1 answer