mysql去除重复记录

业务上需要去除重复记录（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脚本。