Thread: Add column by using SELECT statement

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

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