今天碰到一个更新MySQL数据库数据的问题,请教一下大家:
表:
要求:根据字段2分组,然后修改对应id最大的那条将字段3修改成1,结果如下:
使用下面的sql执行,在oracle上是好的,但在mysql上报错
错误信息:
这个SQL该怎么写呢?
1
heaton_nobu 2015-05-29 13:21:36 +08:00
不太熟悉mysql,但是sqlserver的语法应该是:
1. update [table] set ...... where .... 2. update t set ... from [table] t where .... |
2
lianyue 2015-05-29 13:25:18 +08:00
update 不能别名 update test set test.xxxx = xxx or update test set xxx = xxxx 只能这样其他问题不知道
|
3
Septembers 2015-05-29 13:25:41 +08:00
|
4
Septembers 2015-05-29 13:31:57 +08:00
|
5
lianyue 2015-05-29 13:31:58 +08:00
好像是不能别名忘记了 你测试去掉别名看下 或者别名 加上 test as t
|
6
tomoya92 OP @heaton_nobu @lianyue @Septembers
感谢关注,问题解决了,问题里描述的sql执行报错,不过在oracle里执行是好的 然后在群里问了下,大家一块试出来了,下面这条sql可以解决 UPDATE 表 set 字段3 = 1 where id in ( select a.id from (SELECT max(id) as id FROM 表 GROUP BY 字段2) a ); |
7
b821025551b 2015-05-29 13:44:00 +08:00
文档是这样写的:
In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms: DELETE FROM t WHERE ... (SELECT ... FROM t ...);UPDATE t ... WHERE col = (SELECT ... FROM t ...);{INSERT|REPLACE} INTO t (SELECT ... FROM t ...); Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example: UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...); Here the prohibition does not apply because a subquery in the FROM clause is materialized as a temporary table, so the relevant rows in t have already been selected by the time the update to t takes place. 把update的条件里查询出来的东西起个别名,用这个别名做条件更新,貌似只有mysql是这样 |