Update Int Column in Table with Unique Incrementing Values

Update int column in table with unique incrementing values

declare @i int  = (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices)

update prices
set interfaceID  = @i , @i = @i + 1
where interfaceID is null

should do the work

Update int column in table with incrementing value

with cte as 
( select prodID, row_number() over (order by prodID) as rn 
  from table 
)
update cte set prodID = rn + 9

How to update string values with incrementing numbers

You can solve this task with the row_number() function and therefore without a loop. Something like this:

drop table if exists #people_test 
go
create table #people_test  (UserName varchar (1000))
go
INSERT INTO #people_test (UserName) values ('admin'),('test'),('opq'),('mn'),('ijkl'),('efgh'),('abcd')
GO

update t
    set t.UserName = 'dummy' + cast(t.rn as varchar(10))
from (
    select row_number() over (order by UserName ) rn, * 
    from #people_test
    where UserName!='admin'
) t

select * from #people_test
order by UserName

Update multiple records with different incremental values

You can use a CTE as a more convenient way to perform an UPDATE:

;WITH ToUpdate AS (
  SELECT p.CODE AS CODE, 
         ROW_NUMBER() OVER (ORDER BY CODE) + t.maxVal  AS newCode
  FROM Plant_Component AS p
  CROSS JOIN (
     SELECT MAX(CAST(CODE AS integer)) AS maxVal
     FROM Plant_Component
     WHERE ISNUMERIC(CODE) = 1 AND CODE NOT LIKE  '%[^0-9]%') AS t
  WHERE p.CODE = 'testcode'
)  
UPDATE ToUpdate
SET CODE = CAST(newCode AS VARCHAR(100))

Note: You should CAST or CONVERT using a length parameter as well. I assume here that your CODE field is of type VARCHAR(100).

Update int column values mysql

If you are running MySQL 8.0, you can use row_number() for this. Assuming that you have a unique, ordering column called id, and that you want to update column new_id:

update mytable t
inner join (select id, row_number() over(order by id desc) new_id from mytable) x
    on t.id = x.id
set t.new_id = x.new_id

In earlier versions, one option is to emulate the window function with a user variable:

update mytable t
inner join (
    select t.id, @new_id := @new_id + 1 new_id
    from (select id from mytable order by id desc) t
    cross join (select @new_id := 0) x
) x on t.id = x.id
set t.new_id = x.new_id

---

Related Topics