update with subselect (long) - Mailing list pgsql-sql

Hello all,
I'm working with a genealogy database where I try to implement a 
somewhat unconventional model for names. The purpose is to allow 
different naming styles, especially the old Norwegian naming style with 
Given name/Patronym/Toponym instead of the Given/Patronym style that 
appears as God-given by current genealogy software. To that end, I have 
designed some tables in this way:

CREATE TABLE name_part_types ( -- a key/label pair   name_part_type_id INTEGER PRIMARY KEY,   name_part_type
VARCHAR(50)
);


CREATE TABLE names ( -- one person can have multiple names   name_id INTEGER PRIMARY KEY,   person_id INTEGER
REFERENCESpersons,   is_primary BOOLEAN NOT NULL -- but one must be primary
 
);


CREATE TABLE name_parts ( -- a name has multiple name-parts   name_part_id INTEGER PRIMARY KEY,   name_id INTEGER
REFERENCESnames,   name_part_type INTEGER REFERENCES name_part_types,   name_sequence INTEGER NOT NULL, -- internal
sortorder of name part   name_part VARCHAR(100)
 
);

My name_part_types table presently looks like this:

slekta=> select * from name_part_types;name_part_type_id | name_part_type
-------------------+----------------                1 | prefix                2 | given                3 | surname
         4 | suffix                5 | patronym                6 | toponym
 
(6 rows)

My current genealogy program, The Master Genealogist (TMG), stores names 
in the conventional pigeon-hole way, within the fields Prefix / Given / 
Surname / Suffix. This form is quite awkward regarding old Norwegian 
naming practice, and I have been using the Surname field mainly for 
recording patronyms, and the Suffix field for toponyms (ie. "farm 
names"). I've written a FoxPro to SQL conversion script (using Perl and 
the XBase module) to dump the data from the TMG database. A typical 
name_parts set may look like this:

slekta=> select * from name_parts where name_id = 1652;name_part_id | name_id | name_part_type | name_sequence |
name_part
--------------+---------+----------------+---------------+--------------        3643 |    1652 |              2 |
     0 | Christen        3644 |    1652 |              5 |             1 | Jonsen        3645 |    1652 |
6|             2 | Stavdal
 
(3 rows)

Now I'm starting to approach my point. The values (2,3,4) in the 
name_part_type column should be changed to (2,5,6). As the Suffix field 
in the overwhelming majority of instances is used only if the name is 
on the Given / Patronym / Toponym form, I figure that it should be easy 
to change the name_part_type here. Initially, I ran this update:

slekta=> update name_parts set name_part_type=6 where name_part_type=3;

So far, so good. But how do I change the name_part_type from 3 to 5 for 
the names with the same name_id that were altered by the previous 
command? This is my latest try:

slekta=> begin work;
BEGIN
slekta=> update name_parts set name_part_type=5
slekta-> from (select name_id where name_part_type=6) as gpt_type
slekta-> where name_id=gpt_type and name_part_type=3;
ERROR:  subquery in FROM may not refer to other relations of same query 
level

Ideas, anyone?
-- 
Leif Biberg Kristensen
http://solumslekt.org/