insert row into existing table

[Prizmm]

I have a table with rougly 70 threads, I now need to go back and insert a couple new entries into my table, but they cant go at the end, they need to go in the middle, say like ID 40,41,and 42. How do I do this? Sorry, for the lack of proper terminology. Thanks

 

[vacunita]

Databases have no begining , end, or middle. it's just data to call upon and order as you see fit. The order in which they are inserted does not necessarily reflect the order in which they are displayed.

If you need to insert specific records, with specific keys. Just specify the ID that you want that record to have. as long as it doesn;t already exist.

INSERT INTO table name id,field1,field3,field4 Values('40','somevalue','something else','andother value');

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[Prizmm]

Thanks, thats kind of it, the new records need to go between exisitng records. So I was thinking I could export the DB and then put them in and then import the whole thing back in. I need to add these 3 new records in between existing records. I hope this makes sense. THanks

 

[vacunita]

Just so we are clear:

suppose you have this table:

ID name
1 John
2 Peter
3 Paul
6 Adrian
7 Mary
8 Anne

And you want to insert Jason,and Dean with ID's 4 and 5 in between.

Insert into mytable id,name values('4','Dean');
Insert into mytable id,name values('5','Jason');

Like that?

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[lgarner]

I suppose you could export, order, then import the records. But you'd still have no way of knowing in what order they're stored. Nor would you care. If you select them with "order by id", then that's the order that they're in. If you use "order by name", then they're in name order.

 

[Prizmm]

vacunita, thanks, thats the right track, though in your example there is a gap between 3 and 6, in my problem it would got 3 4 5 6 and I would need to insert a row between 4 and 5. I hope this makes sense. thanks

 

[fredericvandenplas]

if you take care of your ID's yourself, you just could move the the Id's up by adding the number of rows that you want to insert to the Id:
UPDATE set newId=ID+NumberOfrowsToInsert
Of course if it is a linked table it would give you problems as you would have to update it also to the sub-tables.
The problem is that i have no clue why exactly you want to have them inserted in between
explain the logic behind it pls

 

[imstillatwork]

something is fundementaly wrong if you have to do this...you need another field to sort by or something.

Kevin

http://www.lot-o-nothin.com/cfml/

 

[fredericvandenplas]

I agree with imstillatwork
that the table(s) or are wrongly linked or the tables are wrongly designed

 

[guelphdad]

Do you think you have to do this because there are gaps in your auto_increment column of IDs perhaps?

This is the wrong way to approach your database, you should not rely on an auto_increment field for anything other than identifying your rows as unique when there are no other primary key candidates in your rows.

If you are relying on the fact that you want your gaps filled there must be a reason for that other than to make things neat.