我想根据列house_id示例更新自动增量b_id:

id house_id b_id

+-------------------+
|  1   |  H1  |  1  |
|  2   |  H1  |  2  |
|  3   |  H1  |  3  |
|  4   |  H2  |  1  |
|  5   |  H3  |  1  |
+-------------------+


尝试使用此代码并成功生成了b_id,但未更新该表。

SELECT t.id,
       house_id,
       (SELECT count(*)
        FROM House
        WHERE house_id = t.house_id
          AND id <= t.id
       ) AS b_id
FROM House t


所以我尝试了这段代码:

UPDATE House
SET b_id = (SELECT t.id,
                   house_id,
                   (SELECT count(*)
                    FROM House
                    WHERE house_id = t.house_id
                      AND id <= t.id
                   ) AS b_id
            FROM House t);


但是出现错误#1241-操作数应包含1列。

我在Internet上进行浏览,但并没有真正理解并且无法与我的sql代码相关。任何想法?

最佳答案

您的select中有3列要更新单个列,这就是为什么会出错的原因。您可以将Joins用于update。如果您正在使用MYSQL 8.0,则可以使用以下查询来更新计数。

UPDATE
    house AS h
JOIN
    (SELECT id, house_id, row_number() over (partition by house_id) as cnt
     from house) AS g
    on g.id =h.id
SET
    h.b_id = g.cnt ;


Check Demo here

我也使用joins更改了您的查询

UPDATE
    house AS p
JOIN
    (SELECT t.id,(SELECT count(*) FROM house
                   WHERE house_id = t.house_id
                    AND id <= t.id) AS b_id
     FROM house t) AS g
ON p.id = g.id
SET
    p.b_id = g.b_id ;


Demo with your query

关于mysql - MySQL错误:#1241-操作数应包含1列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/58909002/

10-12 12:30
查看更多