SQL: UPDATE with INNER JOIN with LIMIT

Question

I am trying to update specific column in my database.

This query works:

UPDATE table1 A INNER JOIN table2 B 
ON A.type = B.typeName
SET A.closed = 0, A.sample = 0 
WHERE A.`status` IN ('Finished', 'Exception', 'Query') AND A.date BETWEEN '2013-01-01' AND '2013-01-31' 
AND A.code IN ('ex1','ex2','ex3') 
AND A.closed = 0 AND B.order = 'Non-Order' AND A.userName = 'test';

But when I tried to put a limit, it says:

Incorrect usage of UPDATE and LIMIT

UPDATE table1 A INNER JOIN table2 B 
ON A.type = B.typeName
SET A.closed = 0, A.sample = 0 
WHERE A.`status` IN ('Finished', 'Exception', 'Query') AND A.date BETWEEN '2013-01-01' AND '2013-01-31' 
AND A.code IN ('ex1','ex2','ex3') 
AND A.closed = 0 AND B.order = 'Non-Order' AND A.userName = 'test' LIMIT 3;

How can I make this Update with the limit? thanks a lot!

[EDIT]

I already do what I want, but it is slow, took 6 secs to update 3 rows.

Here's the query:

UPDATE table1 SET closed=1, sample=1
 WHERE id IN (
     SELECT id FROM (
         SELECT id FROM table1 A
         INNER JOIN table2 B ON A.type = B.typeName
         WHERE A.`status` IN ('Finished', 'Exception', 'Query') AND A.date BETWEEN '2013-01-01' AND '2013-01-31' 
    AND A.code IN ('ex1','ex2','ex3') 
    AND A.closed = 0 AND B.order = 'Non-Order' AND A.userName = 'test' LIMIT 3
     ) tmp
 );

How can I optimize this query thanks again!

John Woo · Accepted Answer · 2013-03-01T00:29:01.357

You just can't.

According to MySQL docs for UPDATE:

For the multiple-table syntax, UPDATE updates rows in each table named in
table_references that satisfy the conditions. In this case, ORDER BY and LIMIT
cannot be used.

UPDATE 1

UPDATE  table1 a
        INNER JOIN
        (
            SELECT  id 
            FROM    table1 A
                    INNER JOIN table2 B 
                        ON A.type = B.typeName
            WHERE   A.status IN ('Finished', 'Exception', 'Query') AND 
                    A.date BETWEEN '2013-01-01' AND '2013-01-31' AND 
                    A.code IN ('ex1','ex2','ex3') AND 
                    A.closed = 0 AND 
                    B.order = 'Non-Order' AND 
                    A.userName = 'test' 
            LIMIT   3
        ) tmp ON a.ID = tmp.ID
SET     a.closed = 1, 
        a.sample = 1

score 1 · Answer 2 · answered Feb 28 '13 at 05:35

1

LIMIT can only be used on SELECT statements. If you want to limit the no. of records affected for your UPDATE statement, you'll have to use WHERE.

answered Feb 28 '13 at 05:35

whastupduck

1,156
11
25

that's not true. `LIMIT` can still be used on `UPDATE` statements but not when updating multiple tables. – John Woo Feb 28 '13 at 05:36
@JW. Any reference would be appreciated. – whastupduck Feb 28 '13 at 05:39
I have a program that uses LIMIT when updating, but not with inner join – jomsk1e Feb 28 '13 at 05:41
Oh okay. Also found another reference http://stackoverflow.com/questions/1513206/update-multiple-rows-using-limit-in-mysql – whastupduck Feb 28 '13 at 05:43

SQL: UPDATE with INNER JOIN with LIMIT

2 Answers2

Related