How to add lots of values to a Postgres column with a where statement-CodePudding

I have a table with 1000 rows and have added a new column but now i need to add the data to it. Below is an example of my table.

  location       | name   | display_name
----------------- -------- -------
 liverpool       | Dan    | 
 london          | Louise | 
 stoke-on-trent  | Amel   | 
 itchen-hampshire| Mark   |

I then have a csv that looks like this that has the extra data

 location,name,display_name
 Liverpool,Dan,Liverpool
 London,Louise,London
 stoke-on-trent,Amel,Stoke on Trent
 itchen-hampshire,Mark,itchen (hampshire)

i know how to update a single row but not sure for the 1000 rows of data i have?

updating single row

UPDATE info_table
SET display_name = 'Itchen (Hampshire)' 
WHERE id = 'itchen-hampshire';

CodePudding user response：

You should first load that CSV data into another table and then do an update join on the first table:

UPDATE yourTable t1
SET display_name = t2.display_name
FROM csvTable t2
WHERE t2.location = t1.location;

If you only want to update display names which are null and have no value, then use:

WHERE t2.location = t1.location AND display_name IS NULL;

CodePudding user response：

Updating more than one columns you can use this genralized query

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where c.column_b = t.column_b;