Обсуждение: Update a table from another table

Hi,


I am trying to update tableA with records from tableB, in a single SQL
statement, along the lines of

INSERT INTO tablea SELECT * FROM tableb;

but doing UPDATE instead. The manual says

UPDATE [ ONLY ] table [ [ AS ] alias ]
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

I am having trouble defining the SET part of the statement. The table
has many columns. Is there a wild card or something.

Thanks in advance.


--
Jason Tan Boon Teck

Am 11.11.2010 05:25, schrieb Jason Tan Boon Teck:
> I am trying to update tableA with records from tableB, in a single SQL
> statement, along the lines of
>
> INSERT INTO tablea SELECT * FROM tableb;
>
> but doing UPDATE instead. The manual says
>
> UPDATE [ ONLY ] table [ [ AS ] alias ]
>      SET { column = { expression | DEFAULT } |
>            ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
>      [ FROM from_list ]
>      [ WHERE condition | WHERE CURRENT OF cursor_name ]
>      [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
>
> I am having trouble defining the SET part of the statement. The table
> has many columns. Is there a wild card or something.

There is no wildcard mentioned besides the one in the last line, but
this refers to the columns you might want to get returned after the update.

In case you want to replace the whole contents of records you may try to
delete the records in tableA and then reinsert them out of your tableB.
Though this wont work if some other tables have foreign keys of tableA.

Hi Andreas


Thank you for your reply. I've understood and got it working now.


Warm regards,

Jason Tan Boon Teck

On Thu, Nov 11, 2010 at 13:15, Andreas <maps.on@gmx.net> wrote:
> Am 11.11.2010 05:25, schrieb Jason Tan Boon Teck:
>>
>> I am trying to update tableA with records from tableB, in a single SQL
>> statement, along the lines of
>>
>> INSERT INTO tablea SELECT * FROM tableb;
>>
>> but doing UPDATE instead. The manual says
>>
>> UPDATE [ ONLY ] table [ [ AS ] alias ]
>>     SET { column = { expression | DEFAULT } |
>>           ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [,
>> ...]
>>     [ FROM from_list ]
>>     [ WHERE condition | WHERE CURRENT OF cursor_name ]
>>     [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
>>
>> I am having trouble defining the SET part of the statement. The table
>> has many columns. Is there a wild card or something.
>
> There is no wildcard mentioned besides the one in the last line, but this
> refers to the columns you might want to get returned after the update.
>
> In case you want to replace the whole contents of records you may try to
> delete the records in tableA and then reinsert them out of your tableB.
> Though this wont work if some other tables have foreign keys of tableA.
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>



--
Jason Tan Boon Teck