Обсуждение: 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.

On Wed, Oct 15, 2008 at 8:16 AM, Chris Preston
<chris@thetrafalgartravel.com> wrote:
>
> Thanks scott
> Does this work with 8.1 (that's what I'm using)
> When I try to create the b table I get a message

Yep.  Post a self-contained example of what's not working.

> ERROR: there is no unique constraint matching given keys for referenced
> table "testa"
> SQL state: 42830
>
> I named the a table testa and the b table testb

Like it says, there's no matching keys.