Updating a table based on values in the table

asked 2020-08-21T22:42:37.177+00:00
Jim Seidel 61 Reputation points

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?

Azure Database for PostgreSQL
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. answered 2020-08-22T10:54:04.08+00:00
    Anurag Sharma 17,251 Reputation points

    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.