开发者

Archive Parent / Child Table Hierarchy in MySQL

开发者 https://www.devze.com 2023-01-02 21:28 出处:网络
If I have a Parent and Child table in MySQL related by a foreign key, is it possible using a SQL statemen开发者_运维技巧t to move certain rows from Parent and the related rows from Child into archive

If I have a Parent and Child table in MySQL related by a foreign key, is it possible using a SQL statemen开发者_运维技巧t to move certain rows from Parent and the related rows from Child into archive tables (e.g. Parent_Archive and Child_Archive) in an atomic manner?


Use transactions - their whole purpose is to make the series of SQL statements atomic.

For example (NOT very optimized - can be improved with temp table):

START TRANSACTION;

INSERT Child_Archive 
SELECT DISTINCT 
Child.* FROM Child, Parent
WHERE Child.FK = Parent.PK
  AND Parent.something=11; 

DELETE Child WHERE FK IN (
    SELECT DISTINCT PK FROM Parent WHERE Parent.something=11); 

INSERT Parent_Archive
SELECT DISTINCT * FROM Parent WHERE Parent.something=11;

DELETE Parent WHERE Parent.something=11;

COMMIT;
0

精彩评论

暂无评论...
验证码 换一张
取 消