gpt4 book ai didi

sql - 如何在 PostgreSQL 中使用单个查询更新两列

转载 作者:行者123 更新时间:2023-11-29 14:18:34 25 4
gpt4 key购买 nike

我需要根据值更新单个表 (v_default_settings) 中两列(default_setting_valuedefault_setting_enabled)的值同一表中的另一列(default_setting_subcategory)。我仍在学习 PostgreSQL,但是,在我的研究中,这是我提出的两个 UPDATE 语句。我已经针对数据库对它们进行了测试,它们可以单独工作。 我更愿意在单个查询中执行此操作 - 那么我该怎么做呢?

SQL 查询#1

UPDATE v_default_settings
SET default_setting_value = CASE
WHEN default_setting_subcategory = 'base' THEN '/usr'
WHEN default_setting_subcategory = 'bin' THEN ''
WHEN default_setting_subcategory = 'call_center' THEN '/etc/freeswitch/autoload_configs'
WHEN default_setting_subcategory = 'conf' THEN '/etc/freeswitch'
WHEN default_setting_subcategory = 'db' THEN '/var/lib/freeswitch/db'
WHEN default_setting_subcategory = 'dialplan' THEN '/etc/freeswitch/dialplan'
WHEN default_setting_subcategory = 'extensions' THEN '/etc/freeswitch/directory'
WHEN default_setting_subcategory = 'grammar' THEN '/usr/share/freeswitch/grammar'
WHEN default_setting_subcategory = 'log' THEN '/var/log/freeswitch'
WHEN default_setting_subcategory = 'mod' THEN '/usr/lib/freeswitch/mod'
WHEN default_setting_subcategory = 'phrases' THEN '/etc/freeswitch/lang'
WHEN default_setting_subcategory = 'recordings' THEN '/var/lib/freeswitch/recordings'
WHEN default_setting_subcategory = 'scripts' THEN '/usr/share/freeswitch/scripts'
WHEN default_setting_subcategory = 'sip_profiles' THEN '/etc/freeswitch/sip_profiles'
WHEN default_setting_subcategory = 'sounds' THEN '/usr/share/freeswitch/sounds'
WHEN default_setting_subcategory = 'storage' THEN '/var/lib/freeswitch/storage'
WHEN default_setting_subcategory = 'voicemail' THEN '/var/lib/freeswitch/storage/voicemail'
END
WHERE default_setting_subcategory IN ('base','bin','call_center','conf','db','dialplan','extensions','grammar','log','mod','phrases','recordings','scripts','sip_profiles','sounds','storage','voicemail');

SQL 查询#2:

UPDATE v_default_settings
SET default_setting_enabled = CASE
WHEN default_setting_subcategory = 'base' THEN 'true'
WHEN default_setting_subcategory = 'bin' THEN 'true'
WHEN default_setting_subcategory = 'call_center' THEN 'false'
WHEN default_setting_subcategory = 'conf' THEN 'true'
WHEN default_setting_subcategory = 'db' THEN 'true'
WHEN default_setting_subcategory = 'dialplan' THEN 'false'
WHEN default_setting_subcategory = 'extensions' THEN 'false'
WHEN default_setting_subcategory = 'grammar' THEN 'true'
WHEN default_setting_subcategory = 'log' THEN 'true'
WHEN default_setting_subcategory = 'mod' THEN 'true'
WHEN default_setting_subcategory = 'phrases' THEN 'false'
WHEN default_setting_subcategory = 'recordings' THEN 'true'
WHEN default_setting_subcategory = 'scripts' THEN 'true'
WHEN default_setting_subcategory = 'sip_profiles' THEN 'false'
WHEN default_setting_subcategory = 'sounds' THEN 'true'
WHEN default_setting_subcategory = 'storage' THEN 'true'
WHEN default_setting_subcategory = 'voicemail' THEN 'true'
END
WHERE default_setting_subcategory IN ('base','bin','call_center','conf','db','dialplan','extensions','grammar','log','mod','phrases','recordings','scripts','sip_profiles','sounds','storage','voicemail');

最佳答案

使用 array subscriptions会让它更干净

update v_default_settings
set
default_setting_value =
(array['/usr','','/etc/freeswitch/autoload_configs',...])[
array_position (
array['base','bin','call_center',...],
default_setting_subcategory
)
],
default_setting_enabled = ...
where default_setting_subcategory in ('base','bin','call_center',...)

如果你想要一个 case 然后使用更简单的语法:

update v_default_settings
set
default_setting_value = case default_setting_subcategory
when 'base' then '/usr'
when 'bin' then ''
when 'call_center' then '/etc/freeswitch/autoload_configs'
when 'conf' then '/etc/freeswitch'
when 'db' then '/var/lib/freeswitch/db'
...
end,
default_setting_enabled = case
when default_setting_subcategory in ('base','bin','conf',...) then true
else false
end
...
where ...

第二种情况可以使用 in 运算符简单得多

关于sql - 如何在 PostgreSQL 中使用单个查询更新两列,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38490013/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com