pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to add column from old_table to new_table?
9+ messages / 5 participants
[nested] [flat]
* How to add column from old_table to new_table?
@ 2005-09-21 12:36 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-21 12:50 ` Re: How to add column from old_table to new_table? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-21 12:56 ` Re: How to add column from old_table to new_table? A. Kretschmer <akretschmer@despammed.com>
2005-09-21 12:58 ` Re: How to add column from old_table to new_table? Thomas Pundt <mlists@rp-online.de>
2005-09-21 12:59 ` Re: How to add column from old_table to new_table? Gnanavel S <s.gnanavel@gmail.com>
2005-09-21 13:06 ` Re: How to add column from old_table to new_table? Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 5 replies; 9+ messages in thread
From: Joost Kraaijeveld @ 2005-09-21 12:36 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have an old_table with two columns: "id" and "old_attribute". I have
new table with the columns "id" and "new_attribute".
old_table and new_table contain exactly the same id's. Now I want to
copy all the old_attribute from old_table to the corresponding
new_attribute in the new_table
Is that possible in a SQL script (pgadmin or psql console?) and if so,
what is the syntax in this example?
I have tried something as:
insert into new_table(new_attribute)
value( select old_attribute from old_table, new_table where old_table.id
= new_table_id)
where new_table.id = old_table.id
TIA
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to add column from old_table to new_table?
2005-09-21 12:36 How to add column from old_table to new_table? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-09-21 12:50 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-21 13:01 ` Re: How to add column from old_table to new_table? Gnanavel S <s.gnanavel@gmail.com>
4 siblings, 1 reply; 9+ messages in thread
From: Joost Kraaijeveld @ 2005-09-21 12:50 UTC (permalink / raw)
To: pgsql-sql
Hi,
I made a mistake in describing the query I tried. The query I tried was:
update new_table
set new_attribute = (select old_attribute from old_table, new_table
where old_table.id = new_table.id)
where old_table.id = new_table.id
It actually returns the error "ERROR: more than one row returned by a
subquery used as an expression" if ran.
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to add column from old_table to new_table?
2005-09-21 12:36 How to add column from old_table to new_table? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-21 12:50 ` Re: How to add column from old_table to new_table? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-09-21 13:01 ` Gnanavel S <s.gnanavel@gmail.com>
2005-09-21 13:09 ` Re: How to add column from old_table to new_table? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 9+ messages in thread
From: Gnanavel S @ 2005-09-21 13:01 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; +Cc: pgsql-sql
On 9/21/05, Joost Kraaijeveld <J.Kraaijeveld@askesis.nl> wrote:
>
> Hi,
>
> I made a mistake in describing the query I tried. The query I tried was:
>
> update new_table
> set new_attribute = (select old_attribute from old_table, new_table
> where old_table.id = new_table.id)
> where old_table.id = new_table.id
It should be written has
update new_table set new_attribute=old_attribute from old_table where
old_table.id=new_table.id;
It actually returns the error "ERROR: more than one row returned by a
> subquery used as an expression" if ran.
>
>
> --
> Groeten,
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> e-mail: J.Kraaijeveld@Askesis.nl
> web: www.askesis.nl <http://www.askesis.nl;
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to add column from old_table to new_table?
2005-09-21 12:36 How to add column from old_table to new_table? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-21 12:50 ` Re: How to add column from old_table to new_table? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-21 13:01 ` Re: How to add column from old_table to new_table? Gnanavel S <s.gnanavel@gmail.com>
@ 2005-09-21 13:09 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 0 replies; 9+ messages in thread
From: Joost Kraaijeveld @ 2005-09-21 13:09 UTC (permalink / raw)
To: Gnanavel S <s.gnanavel@gmail.com>; Thomas Pundt <thomas.pundt@rp-online.de>; William Leite Araújo <william.bh@gmail.com>; +Cc: pgsql-sql
Thanks all.
Too much coffee, too little sleep.
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to add column from old_table to new_table?
2005-09-21 12:36 How to add column from old_table to new_table? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-09-21 12:56 ` A. Kretschmer <akretschmer@despammed.com>
2005-09-21 13:42 ` Re: How to add column from old_table to new_table? A. Kretschmer <akretschmer@despammed.com>
4 siblings, 1 reply; 9+ messages in thread
From: A. Kretschmer @ 2005-09-21 12:56 UTC (permalink / raw)
To: pgsql-sql
am 21.09.2005, um 14:36:26 +0200 mailte Joost Kraaijeveld folgendes:
> Hi,
>
> I have an old_table with two columns: "id" and "old_attribute". I have
> new table with the columns "id" and "new_attribute".
There are indexes on id?
> Is that possible in a SQL script (pgadmin or psql console?) and if so,
> what is the syntax in this example?
Yes, of course.
>
> I have tried something as:
>
> insert into new_table(new_attribute)
> value( select old_attribute from old_table, new_table where old_table.id
> = new_table_id)
> where new_table.id = old_table.id
Error, because i don't need a insert on new_table. You need a update!
(If there a primary index on id this will raise a error)
update t_new set name = (select name from t_old) where t_old.id=t_new.id;
(t_old and t_new are the tables with (id, name) similar your example)
Regards, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to add column from old_table to new_table?
2005-09-21 12:36 How to add column from old_table to new_table? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-21 12:56 ` Re: How to add column from old_table to new_table? A. Kretschmer <akretschmer@despammed.com>
@ 2005-09-21 13:42 ` A. Kretschmer <akretschmer@despammed.com>
0 siblings, 0 replies; 9+ messages in thread
From: A. Kretschmer @ 2005-09-21 13:42 UTC (permalink / raw)
To: pgsql-sql; +Cc: A. Kretschmer <akretschmer@despammed.com>
am 21.09.2005, um 14:56:25 +0200 mailte A. Kretschmer folgendes:
> update t_new set name = ...
Sorry, wrong. The corect answer is in the tread.
Regards, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to add column from old_table to new_table?
2005-09-21 12:36 How to add column from old_table to new_table? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-09-21 12:58 ` Thomas Pundt <mlists@rp-online.de>
4 siblings, 0 replies; 9+ messages in thread
From: Thomas Pundt @ 2005-09-21 12:58 UTC (permalink / raw)
To: pgsql-sql@postgresql.org, "Joost Kraaijeveld" <J.Kraaijeveld@askesis.nl>
Hi,
On Wednesday 21 September 2005 14:36, Joost Kraaijeveld wrote:
| Is that possible in a SQL script (pgadmin or psql console?) and if so,
| what is the syntax in this example?
funny that you mention pgadmin :-)
| insert into new_table(new_attribute)
| value( select old_attribute from old_table, new_table where old_table.id
| = new_table_id)
| where new_table.id = old_table.id
from the pgadmin help system (reference|sql commands|insert):
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }
and some lines further an example:
This example inserts some rows into table films from a table tmp_films with
the same column layout as films:
INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';
Or - if I was misunderstanding your question and you simply want to update
already existing rows in "new_table", you might try
UPDATE new_table
SET new_attribute = old_table.old_attribute
FROM old_table
WHERE new_table.id = old_table.id;
Ciao,
Thomas
--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to add column from old_table to new_table?
2005-09-21 12:36 How to add column from old_table to new_table? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-09-21 12:59 ` Gnanavel S <s.gnanavel@gmail.com>
4 siblings, 0 replies; 9+ messages in thread
From: Gnanavel S @ 2005-09-21 12:59 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; +Cc: pgsql-sql
On 9/21/05, Joost Kraaijeveld <J.Kraaijeveld@askesis.nl> wrote:
>
> Hi,
>
> I have an old_table with two columns: "id" and "old_attribute". I have
> new table with the columns "id" and "new_attribute".
>
> old_table and new_table contain exactly the same id's. Now I want to
> copy all the old_attribute from old_table to the corresponding
> new_attribute in the new_table
Why?. You can simply rename the old_table to new_table including column
names (if any).
Is that possible in a SQL script (pgadmin or psql console?) and if so,
> what is the syntax in this example?
>
> I have tried something as:
>
> insert into new_table(new_attribute)
> value( select old_attribute from old_table, new_table where old_table.id
> = new_table_id)
> where new_table.id = old_table.id
You need an update statement and not an insert statement.
TIA
>
> --
> Groeten,
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> e-mail: J.Kraaijeveld@Askesis.nl
> web: www.askesis.nl <http://www.askesis.nl;
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: How to add column from old_table to new_table?
2005-09-21 12:36 How to add column from old_table to new_table? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-09-21 13:06 ` Leif B. Kristensen <leif@solumslekt.org>
4 siblings, 0 replies; 9+ messages in thread
From: Leif B. Kristensen @ 2005-09-21 13:06 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 21 September 2005 14:36, Joost Kraaijeveld wrote:
> Hi,
>
> I have an old_table with two columns: "id" and "old_attribute". I
> have new table with the columns "id" and "new_attribute".
>
> old_table and new_table contain exactly the same id's. Now I want to
> copy all the old_attribute from old_table to the corresponding
> new_attribute in the new_table
test=> create table old_table (
test(> old_table_id integer,
test(> old_attribute char(1)
test(> );
CREATE TABLE
test=> create table new_table (
test(> new_table_id integer,
test(> new_attribute char(1)
test(> );
CREATE TABLE
test=> insert into old_table values (1,'A');
INSERT 807376 1
test=> insert into old_table values (2,'B');
INSERT 807377 1
test=> insert into old_table values (3,'C');
INSERT 807378 1
test=> insert into new_table (new_table_id) values (1);
INSERT 807379 1
test=> insert into new_table (new_table_id) values (2);
INSERT 807380 1
test=> insert into new_table (new_table_id) values (3);
INSERT 807381 1
test=> update new_table set new_attribute =
test-> (select old_attribute from old_table
test(> where old_table_id = new_table_id);
UPDATE 3
test=> select * from new_table;
new_table_id | new_attribute
--------------+---------------
1 | A
2 | B
3 | C
(3 rader)
--
Leif Biberg Kristensen
http://solumslekt.org/
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2005-09-21 13:42 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-21 12:36 How to add column from old_table to new_table? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-21 12:50 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-21 13:01 ` Gnanavel S <s.gnanavel@gmail.com>
2005-09-21 13:09 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-21 12:56 ` A. Kretschmer <akretschmer@despammed.com>
2005-09-21 13:42 ` A. Kretschmer <akretschmer@despammed.com>
2005-09-21 12:58 ` Thomas Pundt <mlists@rp-online.de>
2005-09-21 12:59 ` Gnanavel S <s.gnanavel@gmail.com>
2005-09-21 13:06 ` Leif B. Kristensen <leif@solumslekt.org>
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