www.webdeveloper.com
Results 1 to 4 of 4

Thread: Copy or Move a record in a table.

  1. #1
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,213

    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?
    Yes, I know I'm about as subtle as being hit by a bus..(\\.\ Aug08)
    Yep... I say it like I see it, even if it is like a baseball bat in the nutz... (\\.\ Aug08)
    I want to leave this world the same way I came into it, Screaming, Incontinent & No memory!
    I laughed that hard I burst my colostomy bag... (\\.\ May03)
    Life for some is like a car accident... Mine is like a motorway pile up...

    Problems with Vista? :: Getting Cryptic wid it. :: The 'C' word! :: Whois?

  2. #2
    Join Date
    Mar 2010
    Posts
    2,803
    Code:
     
    update  myTable
    set myCol = 6
    where myCol = 8;
    you might have to delete the record with the target id first.

  3. #3
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,213
    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.
    Yes, I know I'm about as subtle as being hit by a bus..(\\.\ Aug08)
    Yep... I say it like I see it, even if it is like a baseball bat in the nutz... (\\.\ Aug08)
    I want to leave this world the same way I came into it, Screaming, Incontinent & No memory!
    I laughed that hard I burst my colostomy bag... (\\.\ May03)
    Life for some is like a car accident... Mine is like a motorway pile up...

    Problems with Vista? :: Getting Cryptic wid it. :: The 'C' word! :: Whois?

  4. #4
    Join Date
    Mar 2010
    Posts
    2,803
    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.

    Code:
     
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles