CODEWITHSUNDEEP
mysql
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

Incorrect usage of UPDATE and LIMIT

When I execute the following query:

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

I get error: 

Incorrect usage of UPDATE and LIMIT

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

Upvotes: 1

Views: 3878

Answers (1)

Kickstart
Kickstart

Reputation: 21513

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

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.

Something like this:-

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

Upvotes: 4

Related Questions