newbie question - insert a row

[johnn]

If I have a table with
:
row n
row n+1
:
How can I insert a new row between row n and row n+1? I don't think the ID or key field of these rows will be in order or this is impossible to do unless rewrite the whole data of this table to a new table.

John

[7stud]

Hi,

Your database client does not order rows. Rows are entered wherever it sees fit and the order they are entered in the tables is entirely of no consequence.

[johnn]

Suppose I have an article that has 10 paragraphs. Let's say each paragraph is a row. Later, if I want to insert a new paragraph between the fifth and sixth paragraphs. How do I do it?
Thanks in advance.

John

[freakysid]

Well you have to develop a numbering system that will accommodate this. Back in to good old days of coding Basic on my Commodore64 (yes that's a whopping 64K of RAM) every line of code had to be numbered (so you could create messy spagetti code with GOTO commands; eg, GOTO 40). A general convention at the time was to increment the line numbers (as you were writing the code) by a factor of 10; eg, 10, 20, 30, 40, ..., so that there was some scope to insert additional lines of code.

Another convention you may want to adopt that would be easier is to make your paragraph number field a float that way you could have 1,2,3,4,5 etc, but later on insert a 2.1, 2.2 etc and still be able to maintain order.

Just a couple of thoughts. Otherwise you have to develop some code so that when you want to insert a new paragraph between some others you update all the paragraph numbers to keep everything in sequence. Eg, You want to insert between 5 and 6. So the new record will become 6 and 6 ->7, 7-> 8, etc

UPDATE tableName
SET paraNum = paraNum + 1
WHERE paraNum >= 6

Now you will have a "slot" where you can insert the new paragraph 6

INSERT INTO tableName
SET paraNum = 6, etc, etc