Reputation: 567
I need to update values of two columns (default_setting_value, and default_setting_enabled) in a single table (v_default_settings) based on the value of another column within the same table (default_setting_subcategory). I'm still learning PostgreSQL, however, in my research these are the two UPDATE statements I have come up with. I have tested them against the database, and they work individually. I would much rather prefer to do this in a single query - so how can I do that?
SQL QUERY #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 QUERY #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');
Upvotes: 0
Views: 319
Reputation: 567
This is what I finally went with (at least that I could understand at the moment). Posting it in case someone else finds it useful:
UPDATE v_default_settings
SET default_setting_value = v.value,
default_setting_enabled = v.enabled
FROM (VALUES
('base', '/usr', 'true' ),
('bin', '', 'true' ),
('call_center', '/etc/freeswitch/autoload_configs', 'false'),
('conf', '/etc/freeswitch', 'true' ),
('db', '/var/lib/freeswitch/db', 'true' ),
('dialplan', '/etc/freeswitch/dialplan', 'false'),
('extensions', '/etc/freeswitch/directory', 'false'),
('grammar', '/usr/share/freeswitch/grammar', 'true' ),
('log', '/var/log/freeswitch', 'true' ),
('mod', '/usr/lib/freeswitch/mod', 'true' ),
('phrases', '/etc/freeswitch/lang', 'false'),
('recordings', '/var/lib/freeswitch/recordings', 'true' ),
('scripts', '/usr/share/freeswitch/scripts', 'true' ),
('sip_profiles', '/etc/freeswitch/sip_profiles', 'false'),
('sounds', '/usr/share/freeswitch/sounds', 'true' ),
('storage', '/var/lib/freeswitch/storage', 'true' ),
('voicemail', '/var/lib/freeswitch/storage/voicemail', 'true' )
) AS v(subcategory,value,enabled)
WHERE v.subcategory = v_default_settings.default_setting_subcategory;
I am still very much appreciative of @siyual and @clodoaldo-neto for assisting with the previous answers.
Upvotes: 0
Reputation: 125574
Using array subscriptions will make it much cleaner
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',...)
If you want a case
then use the simpler syntax:
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 ...
The second case can be much simpler using the in
operator
Upvotes: 2
Reputation: 16917
You just need to add the other column to the SET
statement:
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,
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');
Upvotes: 1