0

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?

I'll-Be-Back
  • 10,530
  • 37
  • 110
  • 213
  • 1
    possible duplicate of [MySQL - UPDATE query with LIMIT](http://stackoverflow.com/questions/6289729/mysql-update-query-with-limit) – Shaeldon Mar 31 '15 at 08:50
  • AFAIK, `LIMIT` isn't used in an update statement. I could be wrong though. – asprin Mar 31 '15 at 08:50

1 Answers1

4

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
Kickstart
  • 21,403
  • 2
  • 21
  • 33