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条)

报告相同问题?

悬赏问题

  • ¥15 office打开卡退(新电脑重装office系统后)
  • ¥300 FLUENT 火箭发动机燃烧EDC仿真
  • ¥15 【Hadoop 问题】Hadoop编译所遇问题hadoop-common: make failed with error code 2
  • ¥15 vb6.0+webbrowser无法加载某个网页求解
  • ¥15 RPA财务机器人采购付款流程
  • ¥15 计算机图形多边形及三次样条曲线绘制
  • ¥15 根据protues画的图用keil写程序
  • ¥200 如何使用postGis实现最短领规划?
  • ¥15 pyinstaller打包错误
  • ¥20 cesm的气溶胶排放文件