CODEWITHSUNDEEP
sql-servert-sql
Fabio Lolli
Fabio Lolli

Reputation: 889

Update multiple records with different incremental values

In this I have a table Plant_Component wich has a CODE column. We inserted in our web application some code to autocompute the value that will go in Plant_Component.CODE for new inserted records, and now I have to do some cleanup and insert the right value in my Plant_Component table with CODE = 'testcode'.

The logic for computing the code is:

The query I used is:

UPDATE Plant_Component
SET CODE = CONVERT(varchar, CONVERT(integer, PCread.CODE) + 1)
FROM  
    (SELECT CODE 
    FROM Plant_Component
    WHERE ISNUMERIC(CODE) = 1
    AND CODE NOT LIKE  '%[^0-9]%'
) AS PCread
WHERE Plant_Component.CODE = 'testcode'

The problem is the same value gets inserted in every row affected by this query. To explain myself, when I tested this my maximum integer value was 507007, and every row affected was updated with the value 507008...

Is there a way to make it so every different row affected will increase this value by 1?

Example: If at the moment I launch the query my max code is 507007, and the query affects three records, I expect these three records to have code respectively 507008, 507009, 507010.

NOTE: I know the way I identify a value as integer isn't foolproof, but this is a one-time-launch script, and I know there will be no values that will cause this to get an error (for example, i think value '9999999999999999999999999999999999' may crash this)

Upvotes: 3

Views: 2402

Answers (4)

Tim James
Tim James

Reputation: 83

You could try using ROW_NUMBER() window function in a CTE to generate sequence numbers for the rows which need to be updated. You could then add that number to the max value of any numeric values in the table.

You don't say which other fields you have available in the table but I have assumed that you have some kind of primary key which will be required to join the CTE to the base table for the UPDATE. My example has PK 'id' of type int. My ROW_NUMBER() window function partitions over the CODE column which means that ROW_NUMBER will reset to 1 for each different non-numeric value of CODE, should you have any.

with cte as (select id, CODE, ROW_NUMBER() over (partition by CODE order by CODE) as rownum
from Plant_Component
WHERE Plant_Component.CODE = 'testcode' )

UPDATE Plant_Component
SET CODE = CONVERT(varchar, rownum + (select max(convert(int,CODE)) from Plant_Component WHERE ISNUMERIC(CODE) = 1))
FROM cte, Plant_Component 
where cte.id = Plant_Component.id

My code may not work exactly in your situation but you might be able to adapt it for your own use.

I hope that helps.

Upvotes: 0

Wajih
Wajih

Reputation: 4393

add this before your code:

DECLARE @a int SET @a = 1

Then update your update section in your code like this:

SET CODE = CONVERT(varchar, CONVERT(integer, PCread.CODE) + @a), @a:=@a+1

Upvotes: 1

Thanigai
Thanigai

Reputation: 31

try this: 

Declare @id int
select @id = isnull(max(code),0)+1 from plant_component
Update plant_component set code = @id, @id=@id+1 where code is null

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

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).

Upvotes: 2

Related Questions