同一行中删除MySQL已选数据 & 1093 - You can‘t specify target table ‘＜table＞‘ for update in FROM clause 解决方案

需求

根据 id 找到 search_num 中含有 docx 的数据，并将其删除

解释

它的意思是说，你不能update你所选择select的值
原因是：你先select出同一表中的某些值，又update这个表(在同一语句中)。

原因

发生错误时，是你将这2个需求写到了同一行的命令中

# 1、选出所需字段
SELECT `search_num` FROM `report` WHERE `id` in (143, 075) AND `search_num` like '%docx';
# 2、根据所需字段，删除
DELETE FROM `report` WHERE `search_num` in ('143_test.docx', '075_test.docx');


# 合并 报错了
DELETE FROM `report` WHERE `search_num` in (SELECT `search_num` FROM`report` WHERE `id` in (143, 075) AND `search_num` like '%docx');
# 为了方便与解决方案比较，添加换行
DELETE FROM `report` WHERE `search_num` in (
	SELECT `search_num` FROM 
		`report` WHERE `id` in (143, 075) AND `search_num` like '%docx'
);

解决方案

通过中间表，绕开同一张表的限制

DELETE FROM `report` WHERE `search_num` in (
    SELECT `search_num` FROM (
        SELECT `search_num` FROM `report` WHERE `id` in (143, 075) AND `search_num` like '%docx'
    ) AS temp
);