February 7, 2019 at 6:29 am
I have a table with an ID column (bigint)
in that table there are a million records but i want to add to extra numbers(99) in front of the ID for a couple of hundred records
So i have the values 100657499, 100658216 etc in the ID column and it has to be 99100657499 and 99100658216.
It's something like this
UPDATE TABLE x
SET ID =
WHERE ID in ('100657499 ', 'etc')
February 7, 2019 at 6:40 am
I think i have it
UPDATE ID
SET ID = REPLICATE('99',10-LEN(RTRIM(ID))) + RTRIM(ID)
WHERE ID in ('100657499 ','etc ')
February 7, 2019 at 7:05 am
leon.booij - Thursday, February 7, 2019 6:29 AMI have a table with an ID column (bigint)
in that table there are a million records but i want to add to extra numbers(99) in front of the ID for a couple of hundred recordsSo i have the values 100657499, 100658216 etc in the ID column and it has to be 99100657499 and 99100658216.
It's something like this
UPDATE TABLE x
SET ID =
WHERE ID in ('100657499 ', 'etc')
Is ID an identity column? If so it will be difficult to update the value. You would need to delete the rows and reinsert them with the new ID.
February 7, 2019 at 7:10 am
Jonathan AC Roberts - Thursday, February 7, 2019 7:05 AMleon.booij - Thursday, February 7, 2019 6:29 AMI have a table with an ID column (bigint)
in that table there are a million records but i want to add to extra numbers(99) in front of the ID for a couple of hundred recordsSo i have the values 100657499, 100658216 etc in the ID column and it has to be 99100657499 and 99100658216.
It's something like this
UPDATE TABLE x
SET ID =
WHERE ID in ('100657499 ', 'etc')Is ID an identity column? If so it will be difficult to update the value. You would need to delete the rows and reinsert them with the new ID.
It isn't an identity column and it worked for me !
February 7, 2019 at 10:04 am
I'd add the new rows before deleting the old ones. You can use SET IDENTITY_INSERT for this. I'd do an insert..select from the old row, replacing the identity with the new value. Once that works, I'd delete the old row and turn IDENTITY_INSERT off.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply