Question: I would like to be able to insert a row into an SQL table, giving it a specified id. The trouble is that the id is unique and for it to work I'd have to update the rows after the inserted row, increasing the id's by 1.

So, say I have a table like the following 

![enter image description here](https://i.stack.imgur.com/HE9xb.png)

I would like to insert the following row (with the id of an existing row)...

![enter image description here](https://i.stack.imgur.com/CYMr1.png)

I want the table to look like this after I have inserted the row...

![enter image description here](https://i.stack.imgur.com/R5HUB.png)

The id's of the rows after the inserted row need to change,like in the above example.

The reason for me doing this is to allow users to order events by importance (I'm using jQuery to order them, just not sure how to do the stuff behind the scenes)

Anything that sets me off in the right direction is appreciated, thanks in advance.

Answer: 
Firstly, best practice is to **never ever** modify the primary key of a row once it is inserted (I assume `id` is your primary key?). *Just Don't Do It™*.

**Instead**, create an additional column (called `importance`/`priority`/something, and modify that field as necessary.

---

That said, the query would look something like:

```
START TRANSACTION;

# increment every importance value that needs incrementing to make way for the new row:
UPDATE mytable SET importance = importance + 1 WHERE importance >= newimportance;

# insert the new row:
INSERT INTO mytable (title, text, importance) VALUES ('newtitle', 'newtext', newimportance);

COMMIT;

```
Something is wrong with your design. An `id` column typically has a unique value that represents each row. The nice thing about `id`s is that the value does *not* change, which means that they can be referenced by other tables or even an application.

It sounds like you want some sort of "sort order" or something like that. I would recommend that you have another column for this purpose. Or, at the very least, rename the `id` column so someone who knows databases will not be confused when looking at your schema. Something called `id` (or `tableID` or something similar) should generally be an auto-incremented primary key.

In any case, you can do what you want by first doing an update:

```
update t
    set newcol = newcol + 1
    where newcol >= NEWVALUE;

insert into t(newcol, . . .)
    select NEWVALUE, . . .;

```