dpr77335 2013-10-27 15:42
浏览 273
已采纳

将数据从一个MySQL表移动到另一个MySQL表

I am trying to move data from one database (registrations) to another when the user clicks a button named delete. (I want to move the data to a table named archived)

Here is what i have tried (found from Google):

 $result=mysql_query("Insert Into archived (select * from registrations WHERE id=$id") ;
 $row = mysql_fetch_array($result);

This doesn't move it... can anyone help?

  • 写回答

3条回答 默认 最新

  • dongsuyou6938 2013-10-27 15:54
    关注

    Firstly you're missing one parenthesis, which you don't have to use in this case at all

    Change your query string to

    Insert Into archived (select * from registrations WHERE id=$id)
                         ^                                        ^
    

    or to just

    Insert Into archived select * from registrations WHERE id=$id
    

    Here is SQLFiddle demo

    Secondly INSERT doesn't return a resultset so you shouldn't use mysql_fetch_array().

    Thirdly if your intent was to move not just to copy data then you also need to delete the row that you copied afterwards.


    Now you can put it all in a stored procedure

    DELIMITER $$
    CREATE PROCEDURE move_to_archive(IN _id INT)
    BEGIN
        START TRANSACTION;
        INSERT INTO archived 
        SELECT * 
          FROM registrations 
         WHERE id = _id;
        DELETE
          FROM registrations 
         WHERE id = _id;
        COMMIT;
    END$$
    DELIMITER ;
    

    Sample usage:

    CALL move_to_archive(2);
    

    Here is SQLFiddle demo

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?