pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedupdate with subselect (long)
4+ messages / 2 participants
[nested] [flat]
* update with subselect (long)
@ 2005-03-17 13:07 Leif B. Kristensen <leif@solumslekt.org>
2005-03-17 13:36 ` Re: update with subselect (long) Leif B. Kristensen <leif@solumslekt.org>
2005-03-17 14:01 ` Re: update with subselect (long) Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 4+ messages in thread
From: Leif B. Kristensen @ 2005-03-17 13:07 UTC (permalink / raw)
To: 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 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
Ideas, anyone?
--
Leif Biberg Kristensen
http://solumslekt.org/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: update with subselect (long)
2005-03-17 13:07 update with subselect (long) Leif B. Kristensen <leif@solumslekt.org>
@ 2005-03-17 13:36 ` Leif B. Kristensen <leif@solumslekt.org>
1 sibling, 0 replies; 4+ messages in thread
From: Leif B. Kristensen @ 2005-03-17 13:36 UTC (permalink / raw)
To: pgsql-sql
On Thursday 17 March 2005 14:07, Leif B. Kristensen wrote:
> slekta=> update name_parts set name_part_type=6 where
> name_part_type=3;
This message was sent a little prematurely while I was editing a similar
posting to comp.databases. The cited line is erroneous and should read:
> slekta=> update name_parts set name_part_type=6 where
> name_part_type=4;
If somebody recognizes my data structure and can recommend some reading
on working with this kind of data, I'd be much obliged.
regards,
--
Leif Biberg Kristensen
http://solumslekt.org/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: update with subselect (long)
2005-03-17 13:07 update with subselect (long) Leif B. Kristensen <leif@solumslekt.org>
@ 2005-03-17 14:01 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-03-17 14:34 ` Re: update with subselect (long) Leif B. Kristensen <leif@solumslekt.org>
1 sibling, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2005-03-17 14:01 UTC (permalink / raw)
To: Leif B. Kristensen <leif@solumslekt.org>; +Cc: 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: update with subselect (long)
2005-03-17 13:07 update with subselect (long) Leif B. Kristensen <leif@solumslekt.org>
2005-03-17 14:01 ` Re: update with subselect (long) Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2005-03-17 14:34 ` Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 0 replies; 4+ messages in thread
From: Leif B. Kristensen @ 2005-03-17 14:34 UTC (permalink / raw)
To: pgsql-sql
On Thursday 17 March 2005 15:01, Stephan Szabo wrote:
> 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;
Thank you very much!
> 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.
Yes, this is a kind of one-shot job. I shall be going through all the
records manually later and correct the ones that don't fit the bill
exactly.
--
Leif Biberg Kristensen
http://solumslekt.org/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-03-17 14:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-17 13:07 update with subselect (long) Leif B. Kristensen <leif@solumslekt.org>
2005-03-17 13:36 ` Leif B. Kristensen <leif@solumslekt.org>
2005-03-17 14:01 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-03-17 14:34 ` Leif B. Kristensen <leif@solumslekt.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox