Обсуждение: update table from a csv file

I have been using COPY FROM to do a mass import of records from CSV files into a new database. I have discover however,
asmall number of records ( a few thousand) in one of the files that contain new data that needs to be added to the
database,but on rows that have a primary key and have already been inserted (so I can't use COPY FROM because it
violatesthe primary key).  

If the structure of the table is

id    data1    data2    data3

and the structure of the CSV file is

id    data1    data2    data3

and I need to update all the rows in data3 where the id = id.

I have created a temporary table and used COPY FROM to load the update data into the temporary table. I seem to be
stuckhowever. I thought I should be able to use the UPDATE command to update all columns and all rows the table.id =
tmp_table.id

Something like:

UPDATE table FROM tmp_table WHERE table.id = tmp_table.id;

Or am I completely off course?

On 12/27/2012 08:27 AM, Kirk Wythers wrote:
> I have been using COPY FROM to do a mass import of records from CSV files into a new database. I have discover
however,a small number of records ( a few thousand) in one of the files that contain new data that needs to be added to
thedatabase, but on rows that have a primary key and have already been inserted (so I can't use COPY FROM because it
violatesthe primary key). 
>
> If the structure of the table is
>
> id    data1    data2    data3
>
> and the structure of the CSV file is
>
> id    data1    data2    data3
>
> and I need to update all the rows in data3 where the id = id.
>
> I have created a temporary table and used COPY FROM to load the update data into the temporary table. I seem to be
stuckhowever. I thought I should be able to use the UPDATE command to update all columns and all rows the table.id =
tmp_table.id
>
> Something like:
>
> UPDATE table FROM tmp_table WHERE table.id = tmp_table.id;
>
> Or am I completely off course?

No. Some questions though.

What version pf Postgres?
Is that the actual UPDATE statement, I see no SET?
Have you tried it?
If so and it failed what was the error?

>


--
Adrian Klaver
adrian.klaver@gmail.com

On 12/27/2012 08:50 AM, Kirk Wythers wrote:
>
> On Dec 27, 2012, at 10:39 AM, Adrian Klaver <adrian.klaver@gmail.com
> <mailto:adrian.klaver@gmail.com>> wrote:
>
>> No. Some questions though.
>
> Thanks for the reply Adrian.
>
>>
>> What version pf Postgres?
>
> 9.1
>
>> Is that the actual UPDATE statement, I see no SET?
>
> I was reading the docs but obviously don't understand the syntax of the
> update statement.
>
>> Have you tried it?
>> If so and it failed what was the error?
>
> Yes and I got an error at or near from. Like this:
>
> b4warmed3=# UPDATE sixty_min FROM tmp_60 WHERE sixty_min.rowid =
> tmp_60.rowid;
> ERROR:  syntax error at or near "FROM"
> LINE 1: UPDATE sixty_min FROM tmp_60 WHERE sixty_min.rowid = tmp_60....
>                           ^
> b4warmed3=#

It is not enough to match the rows through "sixty_min.rowid = tmp_60."
You also need to match the columns using SET.
Per the examples at the bottom of:

http://www.postgresql.org/docs/9.2/interactive/sql-update.html

UPDATE employees SET sales_count = sales_count + 1 FROM accounts
   WHERE accounts.name = 'Acme Corporation'
   AND employees.id = accounts.sales_person;

For many columns it is easier to use the other form of SET. Example from
docs:

UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15,
DEFAULT)


>


--
Adrian Klaver
adrian.klaver@gmail.com