how to add a row somewhere in the middle of a table

Question

Hi folks,

I have a requirement where I have to add a row somewhere in the middle of a table, if it does not exist.

Can you please help me with this?

Answer 1

You can't add a row into an arbitrary position. The rowset in the table in unordered by definition so there are no "positions" in the first place. You can only use some ordering rule for the SELECT queries to retrieve the results in a desired order.

Answer 2

The fact that you asking this question makes me worry that your current code is only working by accident. If you want the rows to appear in a specific order then you need to use an "ORDER BY" on the SQL otherwise you get the rows in an undefined order.

It's likely that the rows will be in some order such as the order you inserted them because that's how it's implemented but that could change at any time if you insert or delete more rows or for no apparent reason.

If the order matters to you then you need to use an order by. And then it will "just work" most likely. But it sounds to me that you need to add that anyway

Answer 3

Not withstanding the technical impossibility of inserting rows in arbitrary positions, it would also violate the 'laws' of normalization.

If you need to order data in a recordset, you must sort it via an ORDER BY statement. This has to be done on data your table contains. If you want to order rows according to user input, eg

1. First Row, [Move Up]/[Move Down]
2. Second Row, [Move Up]/[Move Down]
3. Third Row, [Move Up]/[Move Down]

You should add a column, 'user_sorting', and ensure that user_sorting values are unique versus for subsets accordingly.

Answer 4

As stated previously, you can't technically add stuff in the middle of a table and it's probably just luck how they are sorted in your output (if you're not using a ORDER BY clause).

For the sake of an argument, let's assume you have row_id in your table and it just happens that your rows, through a simple SELECT statement, happen to come in ascending row_id order. If you want to add a row in the middle, you have to increase the row_id of all the rows that you want to be "below" the new row and add the new row with row_id that fits in that gap. Then you also need to update your SELECT statements that output these rows to sort by that row_id (ORDER BY row_id), otherwise this won't work.