How can I insert a new database record in the middle of an existing table?

Tables in SQL don’t have a defined order (they might have, depending on the concrete implementation, but you cannot count on it).

If you require a certain ordering, you must order your result set when selecting.

Therefore, I can see two possibilities:

  1. Update every id above the ‘missing’ row, and insert your new row using the newly ‘freed’ id. This will probably break a lot of other stuff (references to your ids will require updating as well).
  2. Introduce a new column which defines the ordering. This way, ordering can be independent of the row’s id.

Browse More Popular Posts

Leave a Comment