question

JimSeidel-3149 avatar image
0 Votes"
JimSeidel-3149 asked AnuragSharma-MSFT commented

Updating a table based on values in the table

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-postgresql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered AnuragSharma-MSFT commented

Hi @JimSeidel-3149, 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.




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JimSeidel-3149, just wanted to follow up on your query, Please let us know if you are still facing any issues.

0 Votes 0 ·