修数逻辑构成

一个完整的修数过程应该包含如下几部分：
1、数据备份；
2、修数逻辑实现；
3、验证；
4、（出错时执行）回滚。

示例一：

删除单号重复并且没有删除标识和作废的送货单

备份表

备份采购送货单表

CREATE TABLE purchase_delivery_head_0216_2 AS
select * from purchase_delivery_head where delivery_number in (
    SELECT delivery_number FROM purchase_delivery_head where delivery_status!='-1' and is_deleted='0' and create_time>'2023-01-01 00:00:00'  GROUP BY delivery_number HAVING COUNT(*) > 1
);

备份采购送货单明细表

CREATE TABLE purchase_delivery_item_0216_2 AS
select * from purchase_delivery_item where delivery_number in (
SELECT delivery_number FROM purchase_delivery_head where delivery_status!='-1' and is_deleted='0' and create_time>'2023-01-01 00:00:00'  GROUP BY delivery_number HAVING COUNT(*) > 1
);

备份采购送货单详情表

CREATE TABLE purchase_delivery_item_detial_0216_2 AS
select * from purchase_delivery_item_detial where head_id in (
    select id from purchase_delivery_head where delivery_number in (
        SELECT delivery_number FROM purchase_delivery_head where delivery_status!='-1' and is_deleted='0' and create_time>'2023-01-01 00:00:00' GROUP BY delivery_number HAVING COUNT(*) > 1
    )
);

备份供应商送货单表

CREATE TABLE sale_delivery_head_0216_2 AS
select * from sale_delivery_head where delivery_number in (
    SELECT delivery_number FROM purchase_delivery_head where delivery_status!='-1' and is_deleted='0' and create_time>'2023-01-01 00:00:00'  GROUP BY delivery_number HAVING COUNT(*) > 1
);

修数逻辑实现

修复采购送货单表

UPDATE purchase_delivery_head_0216_2 SET is_deleted = 1 WHERE id IN (
    select id from (
                       SELECT a.id FROM purchase_delivery_head_0216_2  a
                                            LEFT JOIN sale_delivery_head_0216_2 b
                                                      ON a.id = b.relation_id
                       WHERE a.delivery_number IN (
                           SELECT delivery_number FROM purchase_delivery_head_0216_2 GROUP BY delivery_number HAVING COUNT(*) > 1
                       )
                         AND b.id is NULL AND a.create_time > '2023-01-01 00:00:00' AND	a.is_deleted = 0) temp
);

delete from purchase_delivery_head where id in (
    select id from purchase_delivery_head_0216_2 where is_deleted=1
);

修复采购送货明细表

UPDATE purchase_delivery_item_0216_2 SET is_deleted = 1 WHERE head_id IN (
    SELECT a.id FROM purchase_delivery_head_0216_2  a
                         LEFT JOIN sale_delivery_head_0216_2 b
                                   ON a.id = b.relation_id
    WHERE a.delivery_number IN (
        SELECT delivery_number FROM purchase_delivery_head_0216_2 GROUP BY delivery_number HAVING COUNT(*) > 1
    )
      AND b.id is NULL AND a.create_time > '2023-01-01 00:00:00' AND	a.is_deleted = 1
);
delete from purchase_delivery_item where id in (
    select id from purchase_delivery_item_0216_2 where is_deleted=1
);

修复采购送货详情表

UPDATE purchase_delivery_item_detial_0216_2 SET is_deleted = 1 WHERE head_id IN (
    SELECT a.id FROM purchase_delivery_head_0216_2  a
                         LEFT JOIN sale_delivery_head_0216_2 b
                                   ON a.id = b.relation_id
    WHERE a.delivery_number IN (
        SELECT delivery_number FROM purchase_delivery_head_0216_2 GROUP BY delivery_number HAVING COUNT(*) > 1
    )
      AND b.id is NULL AND a.create_time > '2023-01-01 00:00:00' AND	a.is_deleted = 1
);

delete from purchase_delivery_item_detial where id in (
    select id from purchase_delivery_item_detial_0216_2 where is_deleted=1
);