你好,我正在尝试使用SQL查询递归更新数据库列。
我有两个表module
和module_dependency
。
第一个表module
有列(带有示例数据):
id, name, state,
1, "website", "installed"
2, "purchase", "installed"
3, "crm", "installed"
4, "sale", "uninstalled"
5, "account", "installed"
6, "website_sale", "installed"
7, "purchase_bonus", "installed"
8, "website_blog", "installed"
9, "sale_discount", "installed"
10, "website_online", "installed"
第二个表格module_dependency
包含如下列(附带示例数据):
id | dependency_name | module_id
----+-----------------+-----------
1 | website_sale | 1
2 | sale_bonus | 4
3 | website_blog | 1
4 | sale_discount | 4
5 | website_online | 1
6 | crm | 10
7 | account | 3
我需要将网站模块的状态更改为"升级",同时也需要更新其所有依赖模块。
我可以检查当前模块的状态:
SELECT * FROM module WHERE name = 'website' AND state = 'installed';
id | name | state
----+---------+-----------
1 | website | installed
为获取网站模块的所有依赖项,我使用以下查询语句:
SELECT m.name, md.dependency_name , md.module_id
FROM module m JOIN module_dependency md ON (m.id = md.module_id)
WHERE m.name = 'website' AND m.state = 'installed';
name | dependency_name | module_id
---------+-----------------+-----------
website | website_sale | 1
website | website_blog | 1
website | website_online | 1
这意味着 website_sale, website_blog 和 website_online 依赖于模块ID为1的 website 模块。
主要问题在于 website_online 依赖于 crm 模块,而 crm 又依赖于 account 模块。
SELECT m.name, md.dependency_name , md.module_id
FROM module m JOIN module_dependency md ON (m.id = md.module_id)
WHERE m.name = 'website_online' AND m.state = 'installed';
name | dependency_name | module_id
----------------+-----------------+-----------
website_online | crm | 10
SELECT m.name, md.dependency_name , md.module_id
FROM module m JOIN module_dependency md ON (m.id = md.module_id)
WHERE m.name = 'crm' AND m.state = 'installed';
name | dependency_name | module_id
------+-----------------+-----------
crm | account | 3
我需要递归地将所有网站依赖项的状态更改为“升级”。更新后的行应该像这样:
id, name, state,
1, "website", "to upgrade"
3, "crm", "to upgrade"
5, "account", "to upgrade"
6, "website_sale", "to upgrade"
8, "website_blog", "to upgrade"
10, "website_online", "to upgrade"
您可以创建带有测试数据的表格:
CREATE TABLE module(id integer, name text, state text);
INSERT INTO module VALUES
(1, 'website', 'installed'),
(2, 'purchase', 'installed'),
(3, 'crm', 'installed'),
(4, 'sale', 'uninstalled'),
(5, 'account', 'installed'),
(6, 'website_sale', 'installed'),
(7, 'purchase_bonus', 'installed'),
(8, 'website_blog', 'installed'),
(9, 'sale_discount', 'installed'),
(10, 'website_online', 'installed');
CREATE TABLE module_dependency(id integer, dependency_name text, module_id integer);
INSERT INTO module_dependency VALUES
(1, 'website_sale', 1),
(2, 'sale_bonus', 4),
(3, 'website_blog', 1),
(4, 'sale_discount', 4),
(5, 'website_online', 1),
(6, 'crm', 10),
(7, 'account', 3);
我尝试编写递归查询,但没有成功。似乎出现了无限循环。如果我能获取模块表的所有模块ID,我可以简单地更新所选条目。
WITH RECURSIVE modules_to_upgrade AS
(
SELECT id
FROM module
WHERE name = 'website'
UNION ALL
SELECT md.module_id
FROM module_dependency md JOIN modules_to_upgrade mtu on mtu.id = md.module_id
)
select * from modules_to_upgrade;