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

On Thu, 17 Mar 2005, Leif B. Kristensen wrote:

> 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 REFERENCES persons,
>     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 REFERENCES names,
>     name_part_type INTEGER REFERENCES name_part_types,
>     name_sequence INTEGER NOT NULL, -- internal sort order 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

The above needs some work.  The below should be acceptable to the system.
update name_parts set name_part_type=5 from (select name_id from
name_parts where name_part_type=6) as gpt_type where
name_parts.name_id=gpt_type.name_id and name_part_type=3;

I'm a bit worried about blindly changing the type for anything that has a
name_part_type=6 record, but given your usage that might be okay.