SQL:使用带有LIMIT的INNER JOIN进行更新 [英] SQL: UPDATE with INNER JOIN with LIMIT

问题描述

我正在尝试更新数据库中的特定列.

I am trying to update specific column in my database.

此查询有效:

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]

我已经做了我想做的，但是很慢，花了6秒钟来更新3行.

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

以下是查询:

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!

推荐答案

您不能.

根据用于更新的MySQL文档:

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. 

更新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

这篇关于SQL:使用带有LIMIT的INNER JOIN进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！