MySQL查询 - 在UPDATE中使用SELECT

I'm trying to select a particular row in a database using SELECT and OFFSET. The result I get is logical and I do get the desired row I want. But then I need to UPDATE that same specific row so I do something like that:

我正在尝试使用SELECT和OFFSET选择数据库中的特定行。我得到的结果是合乎逻辑的,我确实得到了我想要的行。但后来我需要更新相同的特定行,所以我做了类似的事情:

UPDATE table 
SET value=1 
WHERE value IN (SELECT * FROM(
SELECT value FROM table WHERE some criteria LIMIT 1 OFFSET 2) temp_tab);

Now what I expect from this code is to UPDATE the selected row ONLY. Instead it Updates ALL rows in the datatable and sets their value to 1.

现在我对此代码的期望是仅更新所选行。相反,它更新数据表中的所有行并将其值设置为1。

When I use only:

我只使用时:

SELECT * FROM(
    SELECT value FROM table WHERE some criteria LIMIT 1 OFFSET 2) temp_tab

I get only 1 row as the output. (LIMIT 1 OFFSET 2 makes sure I do get the 1 row and it's the 2nd available) I am not exactly sure what I am doing wrong or how I am supposed to achieve this.

我只得到1行作为输出。 (LIMIT 1 OFFSET 2确保我确实获得了第1行并且它是第2行可用)我不确定我做错了什么或我应该如何实现这一目标。

Note: I do have to use SELECT and not some other method using unique ID of the row or something similar. Any help would be greatly appreciated.

注意:我必须使用SELECT而不是使用行的唯一ID或类似的其他方法。任何帮助将不胜感激。

1 个解决方案

#1

First, when using LIMIT and OFFSET you need to use ORDER BY as well. Otherwise the row you get is indeterminate.

首先,当使用LIMIT和OFFSET时,您还需要使用ORDER BY。否则你获得的行是不确定的。

One method uses LIMIT within the UPDATE itself. However, UPDATE doesn't allow OFFSET. So:

一种方法在UPDATE内使用LIMIT。但是,UPDATE不允许OFFSET。所以:

UPDATE table 
    SET value = 1 
    WHERE some criteria
    ORDER BY ??
    LIMIT 1;

The best method would use a unique id. You can do this with the double subquery approach:

最好的方法是使用唯一的id。您可以使用双子查询方法执行此操作:

UPDATE table 
    SET value = 1 
    WHERE id IN (SELECT id
                 FROM (SELECT id
                       FROM table
                       WHERE some criteria
                       ORDER BY ??
                       LIMIT 1 OFFSET 2
                      ) t
                );

If you don't have a single unique id, you can use multiple columns that uniquely define a single row.

如果您没有唯一的唯一ID,则可以使用唯一定义单行的多个列。

#1