From: Gianluca Riccardi <ml-reader@moonwatcher.it>
To: pgsql-sql@postgresql.org
Subject: selective updates
Date: Thu, 15 Dec 2005 16:06:00 +0100
Message-ID: <43A18658.8030202@moonwatcher.it> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ml-reader@moonwatcher.it
Subject: Re: selective updates
In-Reply-To: <43A18658.8030202@moonwatcher.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox