How do I increment all of the stored values of an id field

Question

I have a table called blocks with a primary key blockid. The data starts with a blockid of 0. Now, the problem is that, in another table, the blockid's start at 1. So, I need to get all of the blockid's in blocks to be incremented by 1.
How, do I do this?
Is there a convenient function to do this, or do we need to write one? Or, do I have to go and manually change it all (this wouldn't take too long, but there are more than 100 rows, so, I'd prefer it if I didn't have to do that).

Thanks for everything.

There are some alternative solutions to this question. See: http://stackoverflow.com/questions/3439110/sql-server-update-a-table-by-using-order-by — Ondrej Bozek, Sep 05 '14 at 15:55

score 12 · Accepted Answer · answered Oct 18 '11 at 16:54

Assuming that blockid is not a primary key from your OP, a quick and dirty way could be by running two update statements sequentially:

UPDATE blocks
SET blockid = blockid + 1001

UPDATE blocks
SET blockid = blockid - 1000

In the first UPDATE you increase all of the blockids by 1001. 1000 is just an arbitrarily large number that will allow you to avoid collisions with other ids (assuming you have a uniqueness constraint.)

In the second statement you decrement by 1000 leaving all ids incremented by one while also avoiding collisions.

If 1000 insn't a large enough number you could increase it and make it arbitrarily large. Just make sure to match the number and the math in the 2nd UPDATE.

Yup, that worked wonderfully, thanks for the nice simple solution. — zermy, Oct 18 '11 at 17:01

How do I increment all of the stored values of an id field

1 Answers1