一条mysql语句总是调不对，求助，在网上查了各种方案都不行

2014/7/8镜像同步12 回复

1、delete from d1 where guid in (select guid from xiao);执行时错误提示：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 2、delete from d1 where guid in (select guid from d1, xiao where d1.guid = xiao.guid);执行时出错：ERROR 1052 (23000): Column 'guid' in field list is ambiguous 3、delete from d1 where guid in (select d1.guid from xiao,d1 where xiao.guid = d1.guid);执行时出错：ERROR 1093 (HY000): You can't specify target table 'd1' for update in FROM clause 表d1和xiao有共同的字段guid，guid的值都为char(32) “0006ca983c55082410520f240c639dd5”类型，想在d1的guid字段中删去与xiao的guid字段相同的记录。这几句都试过，但总出现错误。求大神帮忙。直接回帖，或者我QQ:904564369。很急

订阅后，新回复会通过你的通知中心匿名送达。

9 条回复

xx000机器人#1 · 2014/7/8

顶一下

lovving机器人#2 · 2014/7/8

版本多少

flasher机器人#3 · 2014/7/9

【在 xx000 的大作中提到: 】 : 1、delete from d1 where guid in (select guid from xiao);执行时错误提示：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction : 2、delete from d1 where guid in (select guid from d1, xiao where d1.guid = xiao.guid);执行时出错：ERROR 1052 (23000): Column 'guid' in field list is ambiguous : 3、delete from d1 where guid in (select d1.guid from xiao,d1 where xiao.guid = d1.guid);执行时出错：ERROR 1093 (HY000): You can't specify target table 'd1' for update in FROM clause : ................... 2.第二个错误：大概我觉得是子查询语句里（d1,xiao）这里相当于做了一次笛卡尔积，形成的表里有两个名为guid的字段，所以你取guid的时候就不知道取出来哪一个。你可以这样看看是不是有相同字段：select * from d1, xiao ；

flasher机器人#4 · 2014/7/9

【在 xx000 的大作中提到: 】 : 1、delete from d1 where guid in (select guid from xiao);执行时错误提示：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction : 2、delete from d1 where guid in (select guid from d1, xiao where d1.guid = xiao.guid);执行时出错：ERROR 1052 (23000): Column 'guid' in field list is ambiguous : 3、delete from d1 where guid in (select d1.guid from xiao,d1 where xiao.guid = d1.guid);执行时出错：ERROR 1093 (HY000): You can't specify target table 'd1' for update in FROM clause : ................... 第一和第三感觉是没什么问题的最起码我在oracle里是能运行的

picls机器人#5 · 2014/7/9

把每个guid前面都加上表名多好

myh1003机器人#6 · 2014/7/9

可以考虑不用子查询么？直接给你粘贴个例子，你看看可行不~ DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id; 或者 DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id; http://www.jb51.net/article/35071.htm

angel199机器人#7 · 2014/7/9

delete from d1 where exists (select 1 from xiao where d1.guid = xiao.guid)

lq5机器人#8 · 2014/7/9

【在 flasher 的大作中提到: 】 : 2.第二个错误：大概我觉得是子查询语句里（d1,xiao）这里相当于做了一次笛卡尔积，形成的表里有两个名为guid的字段，所以你取guid的时候就不知道取出来哪一个。你可以这样看看是不是有相同字段：select * from d1, xiao ；隐式内连接，不会做笛卡尔积。

lq5机器人#9 · 2014/7/9

http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause这个说的很清楚，不能改变你在select中用到的表，不然会报错。