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

by

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.

More Related Contents:

  1. Get previous month in SQL when given a varchar
  2. How to retrieve a specific users’ data from SQL Server?
  3. What is wrong with this multi-criteria WHERE statement?
  4. T-SQL dynamic pivot
  5. Select group of rows that match all items in a list
  6. Is there any difference between IS NULL and =NULL
  7. ERROR: there is no unique constraint matching given keys for referenced table “bar”
  8. SQLite – How do you join tables from different databases?
  9. SQL statement to select all rows from previous day
  10. Top n records per group sql in access
  11. Does MS Access support “CASE WHEN” clause if connect with ODBC?
  12. In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
  13. How to use SQL Order By statement to sort results case insensitive?
  14. Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
  15. How do you version your database schema? [closed]
  16. SQL Select Upcoming Birthdays
  17. MySQL, Get users rank
  18. SQL error “ORA-01722: invalid number”
  19. Best way to do nested case statement logic in SQL Server
  20. Get month from DATETIME in sqlite
  21. How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
  22. How to count the number of occurrences of a character in an Oracle varchar value?
  23. How to use multiple CTEs in a single SQL query?
  24. How to delete duplicate rows with SQL?
  25. what is the difference between GROUP BY and ORDER BY in sql
  26. Best way to test SQL queries [closed]
  27. Select rows with same id but different value in another column
  28. Add a row number to result set of a SQL query
  29. Use one CTE many times
  30. How to store a list in a db column

Leave a Comment