Message-ID: <43A18658.8030202@moonwatcher.it>
Date: Thu, 15 Dec 2005 16:06:00 +0100
From: Gianluca Riccardi <ml-reader@moonwatcher.it>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; it-IT;
	rv:1.7.8) Gecko/20050927 Debian/1.7.8-1sarge3
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: selective updates
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

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