CODEWITHSUNDEEP
sqlsql-server
Travis J
Travis J

Reputation: 109

How to update a field with a varchar + incrementing int

Im trying to update the values in table to have unique values that are in the format 'UNKNOWN' + 0001 the next one would be 'UNKNOWN' + 0002. im runnign into some trouble when trying to update the field. 

 DECLARE @idtest VARCHAR(15) 
    SET @idtest = '' 
    UPDATE TABLE1 
    SET @idtest = ID = CONVERT(int,'UNKNOWN'+ CAST(@idtest + 1  AS VARCHAR(15))) 
    where ID is null or LTRIM(RTRIM(ID )) = ''
    GO

Any suggestions would be great. I Can only use sql that is supported up to 2008 R2.

UPDATE:

WITH tempUpdateTable AS (
          SELECT t1.*,
                 ROW_NUMBER() over (ORDER BY (SELECT NULL)) AS seqnum
          FROM table1 t1
          WHERE ID IS NULL OR LTRIM(RTRIM(ID)) = ''
        )
        UPDATE tempUpdateTable
        SET ID= 'UNKNOWN' + RIGHT('000' + CAST(seqnum AS VARCHAR(255)), 4);

Upvotes: 0

Views: 843

Answers (2)

Alex
Alex

Reputation: 5167

You can also do this:

DECLARE @idtest INT 
SET @idtest = 0 
UPDATE TABLE1 WITH( TABLOCKX )
SET @idtest = @idtest + 1,
    ID = 'UNKNOWN'+ RIGHT( '0000' + CAST(@idtest AS VARCHAR(15)) , 4 )
where ID is null or LTRIM(RTRIM(ID )) = ''
WITH( MAXDOP = 1 )

This is almost the same as what you had, with the difference being that SQL Server does not support multiple assignments.

Explanation:

  • WITH( TABLOCKX ) - exclusive lock on the table, needed to avoid multiple updates
  • WITH( MAXDOP = 1 ) - Maximum Degree of Parallelism is set to 1. In other words, SQL cannot do any task parallelisation which might lead to the ordering being off.

References: SO Quirky Update, SQLServerCentral Quirky Update

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Use row_number() and an updatable CTE:

with toupdate as (
      select t1.*,
             row_number() over (order by (select NULL)) as seqnum
      from table1
      where ID is null or ltrim(trim(ID )) = ''
     )
update toupdate
    set id = 'UNKNOWN' + right('00000000', cast(seqnum as varchar(255)), 8);

Upvotes: 3

Related Questions