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(at)serensoft(dot)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/ !