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 python天天向上类似问题,但没有清零
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 C#调用python代码(python带有库)
  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)