Re: Updating Query of 1 table from data in another

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