how to do this -- subqueries?

-- main data table
create table person (
	id serial,
	lname varchar,
	primary key ( lname ) -- to keep example simple
);

-- validation table
create table relation_v (
	id serial,
	name varchar
);
insert into relation_v(name)values('Friend');
insert into relation_v(name)values('Colleague');
insert into relation_v(name)values('Family');

-- linking table (person-to-person, many-to-many)
create table relation (
	person     int4 references person ( id ),
	relative   int4 references person ( id ),
	relation_v int4 references relation_v ( id ),
	descr varchar,
	primary key (person,relative,relation_v)
);
...

okay, got that? relation is a linking table for a many-to-many
relationship between PERSON and PERSON. relation_v is the
validation table having valid relation types. joe can be family
and colleague to bob, but since there's no reason to have
joe-bob-family twice, person-relative-relation_v is the primary
key.

select descr
	from
		relation,
		person     p,
		person     r,
		relation_v t
	where
		relation.person     = p.id and p.lname = <$P_NAME> and
		relation.relative   = r.id and r.lname = <$R_NAME> and
		relation.relation_v = t.id and t.name  = <$R_TYPE>

that select properly displays one record, as it should -- we
specify each of the three elements of the primary key, in the
WHERE clause.

so here's the tricky part: i want to fix a relation record --
it's "family" but should be "colleague" instead:

update relation set
	relation_v = (
		-- get the new value we're looking for
		select z.id from relation_v z where name='Colleague'
	)
	from
		person     p,
		person     r,
		relation_v t
	where
		-- make sure we get the one record to update
		relation.person     = p.id and p.lname = <$P_NAME> and
		relation.relative   = r.id and r.lname = <$R_NAME> and
		relation.relation_v = t.id and t.name  = <$R_TYPE>

prodcing ERROR "Cannot insert a duplicate key into unique index"

the WHERE clause is identical, but given the "Cannot insert a
duplicate key into unique index" error, it's apparently finding
more than one record.

(in this test database i've got only three people, and one
relation between each, for each "direction" a-rel-b and b-rel-a
for a total of six relation records.
	person a   relative b    relation_v family
	person b   relative a    relation_v family
	person a   relative c    relation_v boss
	person c   relative a    relation_v employee
	person b   relative c    relation_v colleague
	person c   relative b    relation_v family -- should be colleague
if i were to set ALL "relation_v" values to "Family" (for
example) it should be legal. why "duplicate key" error?)

the trouble is, i need to get the old relation_v.id so i can be
sure i have the one record i'm looking for, and then the new
relation_v.id to set it properly.

which cluestick do i need? (or is there a better paradigm for
this kind of thing?)

-- 
There are 10 kinds of people:
ones that get binary, and ones that don't.
 
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
 
Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !