I created a MYSQL table having 50 rows.
Now I need to add a row between 5 and 6.
But the primary key should change after that 5, new row-6 ,after-wards 7,8
I tried insert row - shows duplicate primary key
How can I add a row between two rows in existing table?
Thanks in advance
Saranya1388
解决方案If I read you right, you have a mySql table with rows thus:
1 me
2 you
3 them
4 him
5 her
6 it
7 whom
8 whoYou want to change this to:
1 me
2 you
3 them
4 him
5 her
6 my new row
7 it
8 whom
9 whoIf this is correct then there is no automatic way to do it. It may not even be possible, depending on your DB design. Did you use auto number? If so, then no, you can''t do it. Is this field used in any other tables? If so, then no, I don''t recommend it.
If not, then you have to:
1) Retrieve all records in the table.
2) Find the last number that needs to be updated.
3) Working backwards from this, increment each number by one, using SQL update commands, until you reach your insert point.
4) Insert your new record.
However, I would really not recommend this. Instead, if you need an order by field, then add one. And update it as described above.
In a real life situation, with multi-user database, this kind of thing is seriously unhealthy for data integrity!