current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →
up vote 0 down vote favorite

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');
share|improve this question
up vote 1 down vote accepted

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');
share|improve this answer
    
thanks that worked! Now I just gotta format it for use in a Bash script. – Kismet Agbasi Jul 20 at 20:33
up vote 2 down vote

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

share|improve this answer
    
Thanks. I think the case helps me visualize it better. Any reason why the array subscription would be the better approach? – Kismet Agbasi Jul 20 at 20:51
    
@KismetAgbasi The array would get rid of the case verbosity – Clodoaldo Neto Jul 20 at 20:53
    
Using the case approach you originally suggested resulted in a syntax error. Noticed you updated your answer while I was typing this.... :) Will try my hands on the array approach. – Kismet Agbasi Jul 20 at 21:02
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.