Copy or Move a record in a table.

[\\.\]

I tried looking for a mysql move command and I got plenty of results on moving tables and databases but nothing that I could see about moving a record in a table.

I have a record, for example id=4759 and the database has 4990 records and the record 4759 needs to be moved to id=4991

Any MOVE or COPY commands exist that I can use in SQL?

[tirna]

 
update  myTable
set myCol = 6
where myCol = 8;

you might have to delete the record with the target id first.

[\\.\]

Cheers, I will give that a try when I got chance to.

TBH it looks too small given some of the threads I have read where simple things need doing and its several lines of code and querys to achieve same deal!

So to recap, if I had


$q = "UPDATE `news_pages` SET `newsid`={$max} WHERE `newsid`=4759;--";

when run would move the content and would that mean that the content at id 4759 is now removed, copied or is no longer in existance? the $max is reference to an empty record, efectively a new insert.

[tirna]

If the column storing your id's is a primary or unique key, then the id target value must not exist otherwise you will get an error.

If you want to set an id in one row to the max value+1 of your id then using a sub query to get the max id of the same table you are updating will probably cause an error.

So a workaround, depending on the number of records and columns in your table, could be to create e temporary table to get the max id and then drop the temp. table.

 
CREATE TABLE junk SELECT * FROM tblperson;
 
update tblperson
set fldPersonID = (select max(fldPersonID) from junk) + 1
where fldPersonID = 4;
 
drop table junk;

things like last_inset_id() might not return the correct value depending on how your table is set up and