I don't understand why my query runs fine in phpMyAdmin and in my php script it gives me the message: can't specify target table for update in FROM clause!!!
我不明白为什么我的查询在phpMyAdmin中运行得很好,在php脚本中,它给了我这样的信息:不能为FROM子句中的update指定目标表!!
update tableA set
Field1 = round(round((select (select br.FieldA from tableB br where tableA.id=br.id and tableA.id2=br.id2) as x),4) - round(tableA.FieldA,4),4)
3 个解决方案
#1
3
That is correct. You cannot select in the nested query the same table you're updating now.
这是正确的。不能在嵌套查询中选择正在更新的同一表。
The better solution will be to use joins in your update. http://dev.mysql.com/doc/refman/5.5/en/update.html
更好的解决方案是在更新中使用连接。http://dev.mysql.com/doc/refman/5.5/en/update.html
#2
1
This should work with multi-table update:
这应该适用于多表更新:
UPDATE tableA, tableB
SET tableA.Field1 = round(round(tableB.FieldA, 4) - round(tableA.FieldA,4), 4)
WHERE tableA.id=tableB.id AND tableA.id2=tableB.id2;
Search for join
in http://dev.mysql.com/doc/refman/5.0/en/update.html
在http://dev.mysql.com/doc/refman/5.0/en/update.html中搜索join
#3
0
you can do it using multiple copies of the same table like this:
您可以使用同一表的多个副本来完成,如下所示:
UPDATE tablename, tablename b
SET tablename.col1 = val
WHERE tablename.col1 = b.col1 AND b.col2 IN (val1,val2,val3);
The val
in the above query can also be a variable like b.col2
.
上述查询中的val还可以是b.col2之类的变量。
#1
3
That is correct. You cannot select in the nested query the same table you're updating now.
这是正确的。不能在嵌套查询中选择正在更新的同一表。
The better solution will be to use joins in your update. http://dev.mysql.com/doc/refman/5.5/en/update.html
更好的解决方案是在更新中使用连接。http://dev.mysql.com/doc/refman/5.5/en/update.html
#2
1
This should work with multi-table update:
这应该适用于多表更新:
UPDATE tableA, tableB
SET tableA.Field1 = round(round(tableB.FieldA, 4) - round(tableA.FieldA,4), 4)
WHERE tableA.id=tableB.id AND tableA.id2=tableB.id2;
Search for join
in http://dev.mysql.com/doc/refman/5.0/en/update.html
在http://dev.mysql.com/doc/refman/5.0/en/update.html中搜索join
#3
0
you can do it using multiple copies of the same table like this:
您可以使用同一表的多个副本来完成,如下所示:
UPDATE tablename, tablename b
SET tablename.col1 = val
WHERE tablename.col1 = b.col1 AND b.col2 IN (val1,val2,val3);
The val
in the above query can also be a variable like b.col2
.
上述查询中的val还可以是b.col2之类的变量。