MySQL 嵌套更新

Question

我的数据库在 timestamp 字段中包含许多错误条目(设置为 0000-00-00 00:00:00，我想要做的是使用最后一个有效时间戳更新此字段。我尝试过此查询，不幸的是没有成功(语法不被接受):

UPDATE `scontrini` s1
SET s1.`timestamp` = 
    (SELECT TOP 1 s2.`timestamp`
        FROM `scontrini` s2
        WHERE s2.`timestamp` <> '0000-00-00 00:00:00' AND s2.`id` < s1.`id`
        ORDER BY s2.`id` DESC)
WHERE `scontrini`.`timestamp`= '0000-00-00 00:00:00';

一些解决方案建议使用内部联接，但我没有测试某些值的相等性。

提前致谢。

编辑

我按照建议将查询更改为:

UPDATE `scontrini` as s1
SET s1.`timestamp` = 
    (SELECT s2.`timestamp`
        FROM `scontrini` as  s2
        WHERE s2.`timestamp` <> '0000-00-00 00:00:00' AND s2.`id` < s1.`id`
        ORDER BY s2.`id` DESC LIMIT 1)
WHERE s1.`timestamp`= '0000-00-00 00:00:00';

但我仍然收到错误消息。

静态分析:1. SELECT附近:发现一条新语句，但该语句与前一条语句之间没有分隔符。2. 在第二个 WHERE 附近:这种类型的子句已经被检查过3. 在第二个 WHERE 附近:语句无法识别。

MySQL 错误消息:

#1093 - Table 's1' is specified twice, both as a target for 'UPDATE' and as a separate source for data

谢谢。

Answer 1

TOP 用于 SQL Server，而不是 MySQL。在子查询末尾使用 LIMIT 来限制返回的行数。

UPDATE `scontrini` s1
SET s1.`timestamp` = 
    (SELECT s2.`timestamp`
        FROM `scontrini` s2
        WHERE s2.`timestamp` <> '0000-00-00 00:00:00' AND s2.`id` < s1.`id`
        ORDER BY s2.`id` DESC LIMIT 1)
WHERE `scontrini`.`timestamp`= '0000-00-00 00:00:00';