CODEWITHSUNDEEP
phpmysqlsql-insert
Josh-Mason
Josh-Mason

Reputation: 319

Insert row into SQL table at a specified index

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

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

enter image description here

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

enter image description here

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.

Upvotes: 2

Views: 4179

Answers (2)

Shai
Shai

Reputation: 7307

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270503

Something is wrong with your design. An id column typically has a unique value that represents each row. The nice thing about ids 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, . . .;

Upvotes: 0

Related Questions