mysql删除数据查询是否在另一表中_SQL如何将将一张表的部分数据查询后插入到另一张表并同时删除从原表查询的数据？...

更新历史表内容

mysql> select * from A;

+----+---------+--------+

| Id | name    | scores |

+----+---------+--------+

|  1 | xiaoyi  |     88 |

|  2 | xiaoer  |     89 |

|  3 | xiaosan |     90 |

|  4 | xiaosi  |     91 |

+----+---------+--------+

4 rows in set (0.00 sec)

mysql> select * from A_his;

+----+---------+--------+

| Id | name    | scores |

+----+---------+--------+

|  1 | xiaoyi  |     88 |

|  2 | xiaoer  |     98 |

|  3 | xiaosan |     87 |

|  4 | xiaosi  |     91 |

+----+---------+--------+

drop procedure if exists sp1;

delimiter //

create procedure sp1(a int)

begin

delete from A_his where Id = a;

insert into A_his select * from A where Id = a;

select * from A_his;

end//

delimiter ;

mysql> call sp1(2); -- 显示更新后的A_his 表的内容

+----+---------+--------+

| Id | name    | scores |

+----+---------+--------+

|  1 | xiaoyi  |     88 |

|  2 | xiaoer  |     89 |

|  3 | xiaosan |     87 |

|  4 | xiaosi  |     91 |

+----+---------+--------+

4 rows in set (0.09 sec)