sorry for posting a new opening 'thread', but my subscribing to the ml
is later than a post with the subject 'APPEND INTO?' dated Thu, 01 Dec
2005 10:32:08 -0500 so i can't make reply to that.
follows a copy of that post
Mark Fenbers <Mark ( dot ) Fenbers ( at ) noaa ( dot ) gov> writes:
> I want to SELECT INTO mytable WHERE (criteria are met), except that I
> want to APPEND into an existing table the rows that are selected,
> instead of creating a new table (which SELECT INTO will do). How can
> this be done?
>INSERT INTO foo SELECT whatever
> regards, tom lane
and what if we need to be selective when inserting rows
let's say we have the following test-db
CREATE TABLE table1 ( column1 int, column2 varchar(20), column3 numeric, column4 int
);
CREATE TABLE table2 ( column1 int, column2 varchar(20)
);
and the population is:
test-db=# SELECT * from table1;column1 | column2 | column3 | column4
---------+-----------------+---------+--------- 1 | some name | 3.5 | 1 | some other | 4.7
| 2 | some some | 3 | 2 | some else | 3 | 3 | some thing | 8.5 | 3
|some thing else | 8.3 |
(6 righe)
test-db=#
test-db=# SELECT * from table2;column1 | column2
---------+------------ 1 | some info 1 | some info1 1 | some info2 2 | some info3 2 | some
info4 2 | some info5
(6 righe)
test-db=#
now we need to update the table1.column4 to store the values from
table2.column1 when table1.column2 table2.column2 match a given citeria
i think we would need a transaction block andor SQL functions, but i
didn't find myself a solution yet.
Gianluca Riccardi
p.s.
i'm using PostgreSQL 7.4.7