业务上需要去除重复记录(dept_code、vehicle_type、weight一样的视为相同记录),逻辑删除,更新记录的状态为不可用(0可用,1不可用)
#1.生成回滚脚本,将以下脚本查询结果复制为insert语句并保存
SELECT
*
FROM
tm_full_load r
WHERE
EXISTS (SELECT 1 FROM (SELECT id FROM tm_full_load WHERE
(
dept_code,
vehicle_type,
weight
) IN (
SELECT
t.dept_code,
t.vehicle_type,
weight
FROM
tm_full_load t
WHERE
t.`status` = 0
GROUP BY
t.dept_code,
t.vehicle_type,
t.weight
HAVING
count(*) > 1
)
AND id NOT IN (
SELECT
min(id)
FROM
tm_full_load
WHERE
`status` = 0
GROUP BY
dept_code,
vehicle_type,
weight
HAVING
COUNT(*) > 1
)
AND `status` = 0
) a
WHERE
r.id = a.id
);
#2.设置重复数据的状态不可用
DELETE
FROM
tm_full_load
WHERE
id IN (
SELECT
b.id
FROM
(
SELECT
*
FROM
tm_full_load r
WHERE
EXISTS (SELECT 1 FROM (SELECT id FROM tm_full_load WHERE
(
dept_code,
vehicle_type,
weight
) IN (
SELECT
t.dept_code,
t.vehicle_type,
weight
FROM
tm_full_load t
WHERE
t.`status` = 0
GROUP BY
t.dept_code,
t.vehicle_type,
t.weight
HAVING
count(*) > 1
)
AND id NOT IN (
SELECT
min(id)
FROM
tm_full_load
WHERE
`status` = 0
GROUP BY
dept_code,
vehicle_type,
weight
HAVING
COUNT(*) > 1
)
AND `status` = 0
) a
WHERE
r.id = a.id
)
) b
);
回滚脚本:
备份的insert脚本。