Execute an update statement in a loop with given pairs of variables

Question

I want to update a column in my database based on a condition in another field. I can do that with two separate queries:

update table_a
    set field_1 = 'English text'
        where ctid in (
            select table_a.ctid from table_a
                left join table_b
                    on table_b.a_id  = table_b.id
                left join table_c
                    on table_c.id = table_b.c_id
            where table_c.language = 'EN'
        );

update table_a
    set field_1 = 'French text'
        where ctid in (
            select table_a.ctid from table_a
                left join table_b
                    on table_b.a_id  = table_b.id
                left join table_c
                    on table_c.id = table_b.c_id
            where table_c.language = 'FR'
        );

However, there are more languages to consider. I'd like to write a script for which I can define a map of language/text pairs and then execute the above statement for each of the pairs. How can I approach this?

I tried looking for PL/pgSQL For loops, but I couldn't apply the examples I could find.

Answer 1

One option would be to declare the pairs of language code/description as rows, then filter and update.

I also suspect that your in condition can be rewritten as exists, without using ctid and re-opening the source table. So:

update table_a as a
set field_1 = v.txt
from ( values 
    ('EN', 'English text'), 
    ('FR', 'French text') 
) as v(code, txt)
where exists ( 
    select 1 
    from table_b as b 
    inner join table_c as c on c.id = b.c_id 
    where c.language = v.code and b.a_id = a.id
)