What I want to do is run a php script that chooses certain mysql entries and just move them to the top of the table(or just auto increment the id column just like when inserting).

Explanation: I have a table with 100 entries. Entry n. 49 and entry 88 have to be updated just like this: entry 49 is deleted, all the data is still in/inserted in the table, but has the id of 101.

the same for entry 88: row with id column of value 88 is deleted/updated, a new entry/same entry updated appears with id of 102, but same data.

Also, no problems like duplicate id should occur after script has ran, asking for a mysql-friendly solution.

Did you understand?

My question is: how can this be done the mo开发者_开发知识库st effective and simple way?

The only option I have in mind is really long and seems unnecessarily complex: select the whole row, delete it, insert a new row with all the data and let the id auto increment itself...

Now if anyone would care to share with me their thoughts on this matter, I would be most grateful!

Other info: script will be run once a day, powerful apache dedicated server. No optimization needed, question asking for simple solution asks for an easy way to deal with my problems without actually needing to work so hard.

Thanks everyone for your help! I appreciate every last piece of your answers and comments! As I think this problem cannot be solved more simply than I stated above, using a piece of PHP, I am closing this question now. Again I say, thanks to everyone that answered! If you have a thought about a solution to the problem either than using php, please answer here and I will read it, or at least maybe we will help any other person who will be reading this thread in the future, assuming 2012 will not bring the end of the world... in that case, nobody will read this thread anymore and you may as well not post at all... but I like to think that the future is ours to decide :P... Thanks again everyone! Cheers!

You know there's an UPDATE type query too right?

WARNING! Pure evil ahead!

UPDATE `table`
SET `field1`='value1', `field2` = 'value2', `id`=LAST_INSERTED_ID()+1
WHERE `id` = 49;

Why the Pure Evil warning? You must NEVER EVER EVER EVER change the ID of an existing entry, as it is bound to mess up your table. The last code was pure theory and is not recommended for use.

Manual Reference

I came across an article explaining A Point In Time database design. The article describes using triggers to implement the design. You might use this solution to create a trigger which, instead of deleting, updates the id.

As stated before, it is really dangerous to mess with your ID-s and will most certainly corrupt your data sooner or later.

I don't really know why do you need it, but this would be the best query I think:

UPDATE %table% SET id = (SELECT Auto_increment FROM information_schema.tables WHERE table_schema = DATABASE() && table_name = '%table%') WHERE id = %id%

If you want to be sure that the DB won't get messed up, use transaction (InnoDB) or LOCK (MyISAM)

InnoDB

START TRANSACTION;
UPDATE %table% SET id = (SELECT Auto_increment FROM information_schema.tables

WHERE table_schema = DATABASE() && table_name = '%table%') WHERE id = %id%; COMMIT;

MyISAM

LOCK TABLES %table% WRITE;
UPDATE %table% SET id = (SELECT Auto_increment FROM information_schema.tables

WHERE table_schema = DATABASE() && table_name = '%table%') WHERE id = %id%; UNLOCK TABLES;