Thread: Updating Query of 1 table from data in another

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hello all, I’m still new to postgres</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">If I have 2 tables with the following data structure</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Agentno and agentname (along with many other fields) this table is called
agent_master</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">And I have another table with agentno and agentname. Table called updatetable</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">When I add data in the updatetable, I want to write a query that will enter the agentname field in
thecorresponding agent_master.agentname field based on agent_master.agentno = updatetable.agentno</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Thanks in advance</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font></div>

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.