On Mon, Oct 13, 2008 at 1:23 PM, Chris Preston <[EMAIL PROTECTED]> wrote: > Hello all, I'm still new to postgres > > If I have 2 tables with the following data structure > > Agentno and agentname (along with many other fields) this table is called > agent_master > > And I have another table with agentno and agentname. Table called > updatetable > > When I add data in the updatetable, I want to write a query that will enter > the agentname field in the corresponding agent_master.agentname field based > on agent_master.agentno = updatetable.agentno
Would an on update cascade foreign key work for you? create table a (id int primary key, nam text not null); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a" CREATE TABLE create table b (aid int, nam text, foreign key (aid,nam) references a(id,nam) on update cascade); CREATE TABLE insert into b (aid, nam) values (1,'steve'); ERROR: insert or update on table "b" violates foreign key constraint "b_aid_fkey" DETAIL: Key (aid,nam)=(1,steve) is not present in table "a". insert into a (id, nam) values (1,'steve'); INSERT 0 1 insert into b (aid, nam) values (1,'steve'); INSERT 0 1 select * from a join b on a.id=b.aid; id | nam | aid | nam ----+-------+-----+------- 1 | steve | 1 | steve update a set nam='scott' where id=1; UPDATE 1 select * from a join b on a.id=b.aid; id | nam | aid | nam ----+-------+-----+------- 1 | scott | 1 | scott tada... hope that makes sense. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
