selective updates

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