mariadb 基于同一表中的选择更新列

qlfbtfca 于 2023-03-30 发布在其他

关注(0)|答案(2)|浏览(144)

我有一个按名为position的列排序的表：

+------------+-------+----------+
| identifier | name  | position |
+------------+-------+----------+
|          1 | test1 |       23 |
|          2 | test4 |      144 |
|          3 | test2 |       68 |
|          4 | test3 |       96 |
+------------+-------+----------+

我正尝试编写一个查询，它使用position列将各行隔开一个固定的数字偏移量，该偏移量基于已经存在的顺序（按position排序）。
假设我想用50的偏移量来分隔位置，表应该像这样更新

+------------+-------+----------+
| identifier | name  | position |
+------------+-------+----------+
|          1 | test1 |       50 |
|          2 | test4 |      200 |
|          3 | test2 |      100 |
|          4 | test3 |      150 |
+------------+-------+----------+

这个任务很容易通过代码中的一个简单for循环来解决

rows = SELECT * FROM table ORDER BY position
for (i = 0; i < count(rows); i++) {
    UPDATE table SET position = ((i + 1) * 50) WHERE identifier = rows[i].identifier
}

但是由于表可能包含许多行，我想直接编写一个SQL查询，我使用的是MariaDB 10。
为清晰起见编辑
运行UPDATE table SET position = position * 50将按照插入的自然顺序更新行。我只需要保持ORDER BY position的顺序，但将位置列偏移一个前缀值。

mariadb

来源：https://stackoverflow.com/questions/75835120/update-colum-based-on-a-select-in-the-same-table

2条答案

按热度按时间

yqyhoc1h1#

解决方法：

UPDATE table AS oo, (SELECT identifier, (ROW_NUMBER() OVER (ORDER BY position ASC) * 50) AS newposition FROM table) AS np
SET oo.position = np.newposition
WHERE oo.identifier = np.identifier

赞(0）回复(0）举报 2023-03-30

13z8s7eq2#

您可以使用一个子查询row_number over parttion来获取新的位置

CREATE TABLE bal
    ("identifier" int, "name" varchar(5), "position" int)
;
    
INSERT INTO bal
    ("identifier", "name", "position")
VALUES
    (1, 'test1', 23),
    (2, 'test4', 144),
    (3, 'test2', 68),
    (4, 'test3', 96)
;

CREATE TABLE

INSERT 0 4

UPDATE bal
SET position = rn * 50
FROM (SELECT position as pos, row_number() OVER(ORDER By position) rn FROM bal) sub
WHERE bal.position = sub.pos;

UPDATE 4

SELECT * FROM bal

识别符	名称	位置
1	测试1	五十
三	测试2	一百
四	测试3	一百五十
二	测试4	二百

SELECT 4

fiddle

赞(0）回复(0）举报 2023-03-30

我来回答

mariadb 基于同一表中的选择更新列

2条答案

相关问题

热门标签

最新问答