

Inserting a row in a particular place

How do I insert a particular row within an existing table at a particular place between two rows?

Short answer: there is no "position" in a relational table, so you can't.

The long answer acknowledges that rows can be sorted in a SELECT result set by using the ORDER BY clause. Obviously, this requires that you designate at least one column in the ORDER BY clause for the database to sort on. Therefore if you wanted to insert a row into the table so that it is positioned in a particular place, this would make sense only if you provide a value for the particular column that you're going to sort on, such that this value lies between the values of the same column for the two rows that the new row must sort between whenever you run a query using the same ORDER BY clause. Whew!

I don't think you could automatically generate this "in between" value, either. You'd need to provide two values for whatever column you choose, in order to identify the two rows. Perhaps you're thinking of selecting the two rows based on some other column, for example, by providing values for column2 and then calculating an "in between" value for column1 like this query --

select avg(column1)
  from thetable
 where column2 in ('value2A', 'value2B')

This is extremely awkward, because you'd need to round the average to the nearest integer, and then you're still faced with a problem if the value you end up with for column1 already exists in the table!

So in the long run, deciding on an "in between" value for a particular column appears like it will have to be a manual process. To answer your question, though, yes, you can do it, but you need to decide which column you're dealing with, and you'll probably have to run a SELECT ... ORDER BY to determine the values that you want to insert between.

For More Information

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.