错误使用UPDATE和LIMIT [英] Incorrect usage of UPDATE and LIMIT

问题描述

当我执行以下查询时:

UPDATE `table1`
INNER JOIN Address ON Address.Mobile = table1.number
LEFT JOIN tps ON tps.number = table1.number
SET table1.export = '2015-03-31'
WHERE Address.Surname != '' and tps.number is null AND table1.export = '0000-00-00'
limit 100000

我收到错误消息:

Incorrect usage of UPDATE and LIMIT

使用更新联接时，我需要使用限制.如何解决这个问题?

I need to use Limit when using Update join. How to solve this issue?

推荐答案

认为它反对对多表更新语句使用order/limit.

Think it is objecting to the use of order / limit on a multi table update statement.

我建议尝试进行更新，其中键字段位于返回有限记录集的子查询的结果中.这里的一个问题是MySQL不允许更新子查询中的表，但是通常可以通过包含第二个包含子查询的表来解决此问题.

I would suggest trying to to do an update where the key field is in the results of a sub query that returns the limited set of records. One problem here is that MySQL will not allow you to update a table that is also in the sub query, but you can normally get around this by having a 2nd containing sub query.

类似这样的东西:-

UPDATE table1
SET table1.export = '2015-03-31'
WHERE table1.number IN 
(
    SELECT number
    FROM
    (
        SELECT table1.number
        FROM `table1`
        INNER JOIN Address ON Address.Mobile = table1.number
        LEFT JOIN tps ON tps.number = table1.number
        WHERE Address.Surname != '' and tps.number is null AND table1.export = '0000-00-00'
        limit 100000
    ) sub1
) sub2

这篇关于错误使用UPDATE和LIMIT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！