Re: Update a table from another table

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.