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 1

;WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY [KWID] ORDER BY [ID]) AS Ranked
    FROM #XX
)
UPDATE x
SET x.[ORDER] = c.Ranked
FROM #XX AS x
INNER JOIN CTE AS c ON x.ID = c.ID AND x.KWID = c.KWID;

SELECT * FROM #XX;

Answer 2

CREATE TABLE #XX(
   ID    INTEGER  NOT NULL PRIMARY KEY 
  ,KWID  INTEGER  NOT NULL
  ,NAME  VARCHAR(17) NOT NULL
  ,[ORDER] int
);
INSERT INTO #XX(ID,KWID,NAME,[ORDER]) VALUES
 (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);
;with mycte as (
 select *,rank() Over(partition by KWID order by ID ) rn
 from  #XX)

 Merge #XX tgt
 Using mycte src on tgt.ID=src.ID
 When matched then
 Update
 Set  [ORDER]=rn ;

 Select * from #XX


drop  TABLE #XX

Answer 3

In addition, try a compact variant too:

;
with CTE as
(
   select [ORDER], ROW_NUMBER() over(partition by KWID order by ID) as rn
   from #XX
)
update CTE set [ORDER] = rn