2.8 KiB
BEGIN;
CREATE TEMP TABLE tmp_del_device AS SELECT id, virtual_no FROM tb_device WHERE virtual_no = '862639071883120' AND deleted_at IS NULL;
CREATE TEMP TABLE tmp_del_cards AS SELECT id, iccid FROM tb_iot_card WHERE iccid IN ( '89860885192590574651', '89860625750010463442', '8986112520903882441' ) AND deleted_at IS NULL;
CREATE TEMP TABLE tmp_del_device_auth AS SELECT id FROM tb_enterprise_device_authorization WHERE device_id IN (SELECT id FROM tmp_del_device) AND deleted_at IS NULL;
-- 设备授权 UPDATE tb_enterprise_device_authorization SET deleted_at = NOW(), revoked_at = COALESCE(revoked_at, NOW()), updated_at = NOW() WHERE id IN (SELECT id FROM tmp_del_device_auth);
-- 卡授权：这里字段是 card_id，不是 iot_card_id UPDATE tb_enterprise_card_authorization SET deleted_at = NOW(), revoked_at = COALESCE(revoked_at, NOW()), updated_at = NOW() WHERE deleted_at IS NULL AND ( card_id IN (SELECT id FROM tmp_del_cards) OR device_auth_id IN (SELECT id FROM tmp_del_device_auth) );
-- 分配记录：按 asset_type 限定，避免不同表 ID 相同误删 UPDATE tb_asset_allocation_record SET deleted_at = NOW(), updated_at = NOW() WHERE deleted_at IS NULL AND ( (asset_type = 'device' AND asset_id IN (SELECT id FROM tmp_del_device)) OR (asset_type = 'iot_card' AND asset_id IN (SELECT id FROM tmp_del_cards)) OR asset_identifier IN ( '862639071883120', '89860885192590574651', '89860625750010463442', '8986112520903882441' ) );
-- 设备绑卡关系 UPDATE tb_device_sim_binding SET deleted_at = NOW(), bind_status = 2, unbind_time = COALESCE(unbind_time, NOW()), updated_at = NOW() WHERE deleted_at IS NULL AND ( device_id IN (SELECT id FROM tmp_del_device) OR iot_card_id IN (SELECT id FROM tmp_del_cards) );
-- 资产钱包：字段是 resource_type/resource_id UPDATE tb_asset_wallet SET deleted_at = NOW(), updated_at = NOW() WHERE deleted_at IS NULL AND ( (resource_type = 'device' AND resource_id IN (SELECT id FROM tmp_del_device)) OR (resource_type = 'iot_card' AND resource_id IN (SELECT id FROM tmp_del_cards)) );
-- 释放全局唯一标识；这个表没有 deleted_at，物理删 DELETE FROM tb_asset_identifier WHERE identifier IN ( '862639071883120', '89860885192590574651', '89860625750010463442', '8986112520903882441' ) OR (asset_type = 'device' AND asset_id IN (SELECT id FROM tmp_del_device)) OR (asset_type = 'iot_card' AND asset_id IN (SELECT id FROM tmp_del_cards));
-- 删除卡 UPDATE tb_iot_card SET deleted_at = NOW(), updated_at = NOW() WHERE id IN (SELECT id FROM tmp_del_cards) AND deleted_at IS NULL;
-- 删除设备 UPDATE tb_device SET deleted_at = NOW(), updated_at = NOW() WHERE id IN (SELECT id FROM tmp_del_device) AND deleted_at IS NULL;
COMMIT;