Re: Add column by using SELECT statement

In response to John Zhang :
> Hi all,
>
> I was wondering how I can add a column and populate it by some query.
>
> For example:
> TblA (Id, fld1)
> TblB(Id, fld1, fld2)
>
> I have a query:
> SELECT b.fld2
> FROM tblB b
> WHERE condition1
>
> what I want to do is add a column in tblA: fld2
> and polpulate the newly added field with the query
> on tblA.Id=tblB.Id
>
> Any advice? Any input would be much appreciated.

ALTER TABLE and UPDATE:

test=# create table tabla (id int, f1 int);
CREATE TABLE
test=*# create table tablb (id int, f1 int, f2 int);
CREATE TABLE
test=*# insert into tabla values (1,1);
INSERT 0 1
test=*# insert into tabla values (2,2);
INSERT 0 1
test=*# insert into tabla values (3,3);
INSERT 0 1
test=*# insert into tablb values (1,1,1);
INSERT 0 1
test=*# insert into tablb values (2,2,2);
INSERT 0 1
test=*# insert into tablb values (3,3,3);
INSERT 0 1
test=*# alter table tabla add column f2 int;
ALTER TABLE
test=*# commit;
COMMIT
test=# update tabla set f2= tablb.f2 from tablb where tabla.id=tablb.id;
UPDATE 3
test=*# select * from tabla;
id | f1 | f2
----+----+----
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
(3 rows)

HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net