Re: Updating Query of 1 table from data in another

On Mon, Oct 13, 2008 at 1:23 PM, Chris Preston
<chris@thetrafalgartravel.com> 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 | steveupdate 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.