SMW SMW - 5 months ago 9
SQL Question

How to change increasing series of numbers?

I have the following table in PostgreSQL:

item id
123 100-0001
123 100-0002
123 100-0003
.....
123 100-0150


I want to change the id of all records to match 745 till 894

meaning 100-0001 change to 100-0745, 100-0002 change to 100-746 etc...

the final result should be:

item id
123 100-0745
123 100-0746
123 100-0747
.....
123 100-0894


id is of type
citext
.

How can I do that?

Note: table contains more records, they shouldn't be effected.

Answer

I'd break the string up - the first four characters shouldn't be changed (100-). The second part can be converted to an integer, summed with 744 to get the new value, and padded back to four characters:

UPDATE mytable
SET    id = SUBSTR(id, 0, 5) || 
            LPAD((SUBSTR(id, 5)::integer + 744)::varchar, 4, '0')