Thread: How to add column from old_table to new_table?

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 

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

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    === 

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/ ----

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/

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 

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    ===