.版本
1)操作系统
cat /etc/issue
CentOS release 6.6 (Final)
Kernel \r on an \m
cat /proc/version
Linux version 2.6.32-504.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-11) (GCC) ) #1 SMP Wed Oct 15 04:27:16 UTC 2014
2)mysql数据库版本
MySQL --version
MySQL Ver 14.14 Distrib 5.6.26, for linux-glibc2.5 (x86_64) using EditLine wrapper
2.问题描述
2.1 发现问题
在工作过程中遇到过如下的报错:
DELETE FROM cs_user_cp WHERE id IN (
-> SELECT cp.id FROM cs_user_cp cp ,`cs_category_process` p,cs_user u,`cs_position` post WHERE cp.`category_process_id` = p.id AND u.id = cp.user_id AND u.`position_code` = post.id AND p.company_id <> post.company_id);
ERROR 1093 (HY000): You can't specify target table 'cs_user_cp' for update in FROM clause
3.问题分析
这是因为Mysql 中Delete和Update 语句,不允许子查询中出现update和delete要操作表
4.解决方案
我们可以通过如下两种变通的方法来解决该问题
方案1
给子查询外面再加一个查询
DELETE FROM cs_user_cp WHERE id IN(SELECT * FROM (
SELECT cp.id FROM cs_user_cp cp ,`cs_category_process` p,cs_user u,`cs_position` post WHERE cp.`category_process_id` = p.id AND u.id = cp.user_id AND u.`position_code` = post.id AND p.company_id <> post.company_id) s )
方案2
delete 语句中使用join
DELETE cs_user_cp from cs_user_cp join (
SELECT cp.id FROM cs_user_cp cp ,`cs_category_process` p,cs_user u,`cs_position` post WHERE cp.`category_process_id` = p.id AND u.id = cp.user_id AND u.`position_code` = post.id AND p.company_id <> post.company_id) s on cs_user_cp.id=s.id;