我想根据列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/