求 SQL 语句，感兴趣的进来看看吧

[zael]

今天碰到一个更新MySQL数据库数据的问题，请教一下大家：
表：

要求：根据字段2分组，然后修改对应id最大的那条将字段3修改成1，结果如下：

使用下面的sql执行，在oracle上是好的，但在mysql上报错

错误信息：

这个SQL该怎么写呢？

[heaton_nobu]

不太熟悉mysql，但是sqlserver的语法应该是：
1. update [table] set ...... where ....
2. update t set ... from [table] t where ....

[lianyue]

update 不能别名 update test set test.xxxx ＝ xxx or update test set xxx ＝ xxxx 只能这样其他问题不知道

[Septembers]

see https://stackoverflow.com/questions/1262786#1262848

[Septembers]

see https://dev.mysql.com/doc/refman/5.5/en/update.html

[lianyue]

好像是不能别名忘记了 你测试去掉别名看下 或者别名 加上 test as t

[zael]

@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 );

[b821025551b]

文档是这样写的：
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是这样