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