How can a table column's rows be updated beginning from the first row?

Question

How can an SQL query be written to update a table column's rows beginning from the first row, using a txt or csv file?

For example, before the update, the table might look like

column_1   column_2
1          null
2          null
3          null

then after the update, the desired result would be like

column_1   column_2
1          this
2          that
3          those

I've tried inserting the data, but this only appends it to the bottom of the row, rather than replacing the exiting null values.

With the same example input, this output looks like

column_1   column_2
1          null
2          null
3          null
null       this
null       that
null       those

https://stackoverflow.com/questions/18904601/mysql-update-table-using-import-data-from-text-file — Raghavan, Aug 18 '19 at 17:25
For PostgreSQL, directions for how to UPDATE a table from csv or txt file [is found here](https://stackoverflow.com/questions/8910494/). Because you cannot do the update directly from file, you (1) create a temporary table from the text file (2) update the target table from temp table (3) drop temporary table. — bfris, Aug 18 '19 at 17:38

score 0 · Answer 1 · answered Aug 18 '19 at 19:17

Load the data into a table.

Then run an update with a join:

update original o
   set column_2 = t.column_2
   from csv_table t
   where o.column_1 = t.column_1;

For your example, though, it might make more sense to truncate the original table and just copy the new data into it.

score 0 · Answer 2 · answered Aug 19 '19 at 06:09

0

First, create a file_fdw foreign table for the text file.

Then use INSERT ... ON CONFLICT to update the table. This will update rows already present in the table and add the new ones.

answered Aug 19 '19 at 06:09

Laurenz Albe

2 Answers2