[SQL] update with subselect (long)
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/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] update with subselect (long)
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/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] update with subselect (long)
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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] update with subselect (long)
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/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
