Efficient way to map new values to a sql query (i.e 4 columns each with same credentials)

Question

First I am new to Postgres so I may be describing this problem incorrectly so please point me to an answer if it has already been answered.

I have a database with 10 columns. Lets say 4 columns all use the same codes for values (i.e the letter a, b, d, e) I want to rename all of these in the query to what they are matched with values in another table.

I have a very long approach at this stage involving a nested With statements. Is there a easy way to go from say:

table1:

id    | col1 | col2 | col3 | col4
------+------+------+------+------
Row_1 | a    | c    | d    | e
Row_2 | c    | c    | c    | c 
Row_3 | e    | d    | d    | c

Lookup Table:

code | value
-----+---------
a    | apple
b    | banana
c    | catfish
d    | dog
e    | egg

Desired result:

Row_1 | apple   | catfish | dog     | egg
Row_2 | catfish | catfish | catfish | dog
Row_3 | egg     | dog     | dog     | catfish

Answer 1

Yes, https://dba.stackexchange.com/questions/145038/how-to-join-to-the-same-table-multiple-times should basically work for you. But you want an UPDATE, which is a bit different:

Either way, code values in table lookup have to be UNIQUE for obvious reasons.

Updating a single column is simple:

UPDATE table1 t
SET    col1 = l.value
FROM   lookup l
WHERE  t.col1 = l.code;

If no match is found, the row it table1 is not updated. But running separate updates for every column is considerably more expensive than updating all columns at once - which is a bit more tricky:

UPDATE table1 t
SET    col1 = COALESCE(l1.value, t.col1)
     , col2 = COALESCE(l2.value, t.col2)
     , col3 = COALESCE(l3.value, t.col3)
     , col4 = COALESCE(l4.value, t.col4)
FROM   table1 t1
LEFT   JOIN lookup l1 ON t.col1 = l1.code
LEFT   JOIN lookup l2 ON t.col2 = l2.code
LEFT   JOIN lookup l3 ON t.col3 = l3.code
LEFT   JOIN lookup l4 ON t.col4 = l4.code
WHERE  t1.id = t.id;

In this case, all rows are updated. While there can be NULL values in table1 or missing values in lookup we need LEFT [OUTER] JOIN to not exclude rows from the update. So we need another instance of table1 in the FROM clause and LEFT JOIN to that.

And the added COALESCE retains the original value for each individual column where no match was found. The optimal query depends on details not disclosed in your question ...

You might add another WHERE clause to exclude rows where nothing changes. See:

• How do I (or can I) SELECT DISTINCT on multiple columns?