table inheritance question

Posted in 2004

MMUTLANYNE MOJAPELO — Sat, 24 Jan 2004 13:50:28 — source: IIUG Forums & Mailing Lists

HELP !!!!! How does one insert a value into a subtable if the supertable entry already exists. I have tried looking at the IBM manuals but they only give info on the supertables

↪ replying to MMUTLANYNE MOJAPELO

NormaJean.Sebastian@tellabs.com — Sat, 24 Jan 2004 15:51:57 — source: IIUG Forums & Mailing Lists

Hi, I'm not sure what you mean by sub and super table, but if you are talking about foreign key relationships... you could disable constraints, triggers, whatever is in your way regarding those tables for a brief moment, then insert your record. You would need to manually evaluate all the relationships for that record before inserting it so you don't break anything. enable constraints,triggers,etc after you fix it. If you were to do that, for safety, it would be best to do with no other users in the system. and of course, TEST it somewhere before doing in production. Norma Jean -----Original Message----- From: mojapelo@netscape.net [mailto:mojapelo@netscape.net] Sent: Saturday, January 24, 2004 7:50 AM To: ids@iiug.org; forum.subscriber@iiug.org Subject: table inheritance question [2486] HELP !!!!! How does one insert a value into a subtable if the supertable entry already exists. I have tried looking at the IBM manuals but they only give info on the supertables ----------------------------------------- ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================

↪ replying to MMUTLANYNE MOJAPELO

Davis Kwong — Mon, 26 Jan 2004 04:16:48 — source: IIUG Forums & Mailing Lists

--0__=08BBE4B4DF8539628f9e8a93df938690918c08BBE4B4DF853962 Content-type: multipart/alternative; Boundary="1__=08BBE4B4DF8539628f9e8a93df938690918c08BBE4B4DF853962" --1__=08BBE4B4DF8539628f9e8a93df938690918c08BBE4B4DF853962 Content-type: text/plain; charset=US-ASCII Hi you can just insert into the subtable directly, even if the supertable and subtable have identical shapes, for example > create row type type1 (a int); Row type created. > create table tab1 of type type1;Table created. > create row type type2 under type1; Row type created. > create table tab2 of type type2 under tab1;Table created. > insert into tab1 values (1);1 row(s) inserted. > insert into tab2 values (1);1 row(s) inserted. > select * from tab1; a 1 1 2 row(s) retrieved. thanks. davis. "MMUTLANYNE ...." <mojapelo@netscap To: ids@iiug.org e.net> cc: Sent by: Subject: table inheritance question [2486] forum.subscriber@ iiug.org 01/24/2004 05:50 AM HELP !!!!! How does one insert a value into a subtable if the supertable entry already exists. I have tried looking at the IBM manuals but they only give info on the supertables --1__=08BBE4B4DF8539628f9e8a93df938690918c08BBE4B4DF853962 Content-type: text/html; charset=US-ASCII Content-Disposition: inline <html><body> Hi you can just insert into the subtable directly, even if the supertable and subtable have identical shapes, for example > create row type type1 (a int); Row type created. > create table tab1 of type type1; Table created. > create row type type2 under type1; Row type created. > create table tab2 of type type2 under tab1; Table created. > insert into tab1 values (1); 1 row(s) inserted. > insert into tab2 values (1); 1 row(s) inserted. > select * from tab1; a 1 1 2 row(s) retrieved. thanks. davis. <img src="cid:10__=08BBE4B4DF8539628f9e8a93df938@us.ibm.com" width="16" height="16" alt="Inactive hide details for "MMUTLANYNE ...." <mojapelo@netscape.net>">"MMUTLANYNE ...." <mojapelo@netscape.net> <table V5DOTBL=true width="100%" border="0" cellspacing="0" cellpadding="0"> <tr valign="top"><td width="1%"><img src="cid:20__=08BBE4B4DF8539628f9e8a93df938@us.ibm.com" border="0" height="1" width="72" alt=""> </td><td style="background-image:url(cid:30__=08BBE4B4DF8539628f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " width="1%"><img src="cid:20__=08BBE4B4DF8539628f9e8a93df938@us.ibm.com" border="0" height="1" width="225" alt=""> <ul> <ul> <ul> <ul>"MMUTLANYNE ...." <mojapelo@netscape.net> Sent by: forum.subscriber@iiug.org 01/24/2004 05:50 AM</ul> </ul> </ul> </ul> </td><td width="100%"><img src="cid:20__=08BBE4B4DF8539628f9e8a93df938@us.ibm.com" border="0" height="1" width="1" alt=""> To: ids@iiug.org cc: Subject: table inheritance question [2486]</td></tr> </table> <tt>HELP !!!!! How does one insert a value into a subtable if the supertable entry already exists. I have tried looking at the IBM manuals but they only give info on the supertables </tt> </body></html> --1__=08BBE4B4DF8539628f9e8a93df938690918c08BBE4B4DF853962-- --0__=08BBE4B4DF8539628f9e8a93df938690918c08BBE4B4DF853962 Content-type: image/gif; name="graycol.gif" Content-Disposition: inline; filename="graycol.gif" Content-ID: <10__=08BBE4B4DF8539628f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7 --0__=08BBE4B4DF8539628f9e8a93df938690918c08BBE4B4DF853962 Content-type: image/gif; name="ecblank.gif" Content-Disposition: inline; filename="ecblank.gif" Content-ID: <20__=08BBE4B4DF8539628f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=08BBE4B4DF8539628f9e8a93df938690918c08BBE4B4DF853962 Content-type: image/gif; name="pic15717.gif" Content-Disposition: inline; filename="pic15717.gif" Content-ID: <30__=08BBE4B4DF8539628f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhWABDALP/AAAAAK04Qf79/o+Gm7WuwlNObwoJFCsoSMDAwGFsmIuezf///wAAAAAAAAAA AAAAACH5BAEAAAgALAAAAABYAEMAQAT/EMlJq704682770RiFMRinqggEUNSHIchG0BCfHhOjAuh EDeUqTASLCbBhQrhG7xis2j0lssNDopE4jfIJhDaggI8YB1sZeZgLVA9YVCpnGagVjV171aRVrYR RghXcAGFhoUETwYxcXNyADJ3GlcSKGAwLwllVC1vjIUHBWsFilKQdI8GA5IcpApeJQt8L09lmgkH LZikoU5wjqcyAMMFrJIDPAKvCFletKSev1HBw8KrxtjZ2tvc3d5VyKtCKW3jfz4uMKmq3xu4N0nK BVoJQmx2LGVOmrqNjjJf2hHAQo/eDwJGTKhQMcgQEEAnEjFS98+RnW3smGkZU6ncCWav/4wYOnAI TihRL/4FEwbp28BXMMcoscQCVxlepL4IGDSCyJyVQOu0o7CjmLN50OZlqWmyFy5/6yBBuji0AxFR M00oQAqNIstqI6qKHUsWRAEAvagsmfUEAImyxgbmUpJk3IklNUtJOUAVLoUr1+wqDGTE4zk+T6FG uQb3SizBCwatiiUgCBN8vrz+zFjVyQ8FWkOlg4NQiZMB5QS8QO3mpOaKnL0Z2EKvNMSILEThKhCg zMKPVxYJh23qm9KNW7pArPynMqZDiErsTMqI+LRi3QAgkFUbXpuFKhSYZALd0O5RKa2z9EYKBbpb qxIKsjUPRgD7I2XYV6wyrOw92ykExP8NW4URhknC5dKGE4v4NENQj2jXjmfNgOZDaXb5glRmXQ33 YEWQYNcZFnrYcIQLNzyTFDQNkXIff0ExVlY4srziQk43inZgL4rwxxINMvpFFAz1KOODHiu+4aEw NEjFl5B3JIKWKF3k6I9bfUGp5ZZcdunll5IA4cuHvQQJ5gcsoCWOOUwgltIwAKRxJgbIkJAQZEq0 2YliZnpZZ4BH3CnYOXldOUOfQoYDqF1LFHbXCrO8xmRsfoXDXJ6ChjCAH3QlhJcT6VWE6FCkfCco CgrMFsROrIEX3o2whVjWDjoJccN3LdggSGXLCdLEgHr1lyU3O3QxhgohNKXJCWv8JQr/PDdaqd6w 2rj1inLiGeiCJoDspAoQlYE6QWLSECehcWIYxIQES6zhbn1iImTHEQyqJ4eIxJJoUBc+3CbBuwZE V5cJPPkIjFDdeEabQbd6WgICTxiiz0f5dBKquXF6k4senwEhYGnKEFJeGrxUZy8dB8gmAXI/sPvH ESfCwVt5hTgYiqQqtdRNHQIU1PJ33ZqmzgE90OwLaoJcnMop1WiMmgkPHQRIrwgFuNV90A3doNKT mrKIN07AnGcI9BQjhCBN4RfA1qIZnMqorJCogKfGQnxSCDilTVIA0yl5ciTovgLuBDKFUDE9aQcw 9SA+rjSNf9/M1gxrj6VwDTS0IUSElMzBfsj0NFXR2kwsV1A5IF1grLgLL/r1R40BZEnuBWgmQEyb jqRwSAt6bqMCOFkvKFN2GPPkUzIm/SCF8z8pVzpbjVnMsy0vOr1hw3SaSRUhpY09v0z0J1FnwzPl fmh+xl4WtR0zGu24I4KbMQm3lnVu2oNWxI9W/lcyzA+mCKF4DBikxb/+UWtOGRiFP8qEwAayIgIA Ow== --0__=08BBE4B4DF8539628f9e8a93df938690918c08BBE4B4DF853962--

↪ replying to Davis Kwong

Jonathan Leffler — Thu, 29 Jan 2004 01:36:24 — source: IIUG Forums & Mailing Lists

--0__=08BBE4B9DF95E16D8f9e8a93df938690918c08BBE4B9DF95E16D Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable MMUTLANYNE <mojapelo@netscape.net> asked: >HELP !!!!! >How does one insert a value into a subtable if the supertable entry already >exists. > >I have tried looking at the IBM manuals but they only give info on the= >supertables Davis Kwong said: >you can just insert into the subtable directly, even if the supertable= and >subtable have identical shapes, for example > >> create row type type1 (a int); >Row type created. > >> create table tab1 of type type1;>Table created. > >> create row type type2 under type1; >Row type created. > >> create table tab2 of type type2 under tab1;>Table created. > >> insert into tab1 values (1);>1 row(s) inserted. > >> insert into tab2 values (1);>1 row(s) inserted. > >> select * from tab1;> > a > > 1 > 1 > >2 row(s) retrieved. I suspect that MMUTLANYNE has a primary key on the super-table that prevents duplicate entries. I also suspect that the answer is that you have to do a combination of DELETE from super-table and INSERT into sub-table - in a transaction, o=f course. For a single row case - satisfying the criterion WHERE a =3D 1= , one might perhaps do: BEGIN WORK; SELECT FROM tab1 WHERE a =3D 1 INTO TEMP x; DELETE FROM tab1 WHERE a =3D 1; INSERT INTO tab2 SELECT * FROM x;COMMIT WORK; Handling more general cases where the sub-type table has more columns t= han the super-type would be harder - you'd probably start by selecting noth= ing from the sub-table into a temp table (to get the right target table sha= pe, then do the cross-load from the super-type table specifying the column values. At some point, you'd have to set the subtable columns to appropriate non-null values. Then, finally, you could cross-load from = the temp table and commit (or rollback on an error). In passing, I note that the IDS 9.40 Informix Guide to SQL: Syntax manu= al implies (p2-255 of ct1sqna.pdf) that the OF TYPE clause can use.. OF TYPE rowtype ( field-definition-comma-list , multi-column-constraint-comma-list ) UNDER supertable It doesn't say that the field-definition-comma-list (my name for a non-empty comma-separated list of 'field definitions') can be empty, no= r that the constraint list can be empty, which is slightly puzzling, but = more significantly, I've been unable to get any notation that includes the mandatory parentheses - which can nominally be empty - to work. I've t= ried a number of semi-plausible constructs, and none of them worked. I fear= this is a documentation error. CREATE TABLE t1 OF TYPE type1; -- Non-controversial CREATE TABLE t2 OF TYPE type2 UNDER t1; -- Worked - but manual says t=his should fail (empty parens are necessary before UNDER) CREATE TABLE t3 OF TYPE type2 ( ) UNDER t2; -- Failed - but manual =says the parens should be necessary I tried a fairly large number of (c INT NOT NULL) and (c INT NOT NULL, UNIQUE(a, c)) type constructs where the empty parentheses are, and ever= y one which tries to add columns comes back -201 syntax error with IDS 9.40.UC1. As Davis pointed out to me when we chatted about this, it doesn't make sense for a subtable to be able to define additional field= s. That subtable is of type 'rowtype' which means that the subtable's colu= mn definition is a one-to-one match to the rowtype's field definition. Y= ou can (optionally) add a unique constraint, for example. So, the parenth= eses in the syntax diagram should be optional (not mandatory), and the 'field-definition-comma-list' should not be part of the syntax. I've reported this to the docinf@us.ibm.com mailing list, so the documentati= on will be updated in due course. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!"= --0__=08BBE4B9DF95E16D8f9e8a93df938690918c08BBE4B9DF95E16D Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable <html><body> <tt>MMUTLANYNE <mojapelo@netscape.net> asked: >HELP !!!!! >How does one insert a value into a subtable if the supertable entry= already >exists. > >I have tried looking at the IBM manuals but they only give info on = the >supertables</tt> Davis Kwong said: <tt> >you can just insert into the subtable directly, even if the superta= ble and >subtable have identical shapes, for example > >> create row type type1 (a int); >Row type created. > >> create table tab1 of type type1; >Table created. > >> create row type type2 under type1; >Row type created. > >> create table tab2 of type type2 under tab1; >Table created. > >> insert into tab1 values (1); >1 row(s) inserted. > >> insert into tab2 values (1); >1 row(s) inserted. > >> select * from tab1; > >          a > >          1 >          1 > >2 row(s) retrieved. </tt> I suspect that <tt>MMUTLANYNE</tt> has a primary key on the super-table= that prevents duplicate entries. I also suspect that the answer is that you have to do a combination of = DELETE from super-table and INSERT into sub-table - in a transaction, o=f course. For a single row case - satisfying the criterion WHERE a =3D= 1, one might perhaps do: <ul> BEGIN WORK; SELECT FROM tab1 WHERE a =3D 1 INTO TEMP x; DELETE FROM tab1 WHERE a =3D 1; INSERT INTO tab2 SELECT * FROM x; COMMIT WORK; </ul> Handling more general cases where the sub-type table has more columns t= han the super-type would be harder - you'd probably start by selecting = nothing from the sub-table into a temp table (to get the right target t= able shape, then do the cross-load from the super-type table specifying= the column values. At some point, you'd have to set the subtable colu= mns to appropriate non-null values. Then, finally, you could cross-loa= d from the temp table and commit (or rollback on an error). In passing, I note that the IDS 9.40 Informix Guide to SQL: Syntax manu= al implies (p2-255 of ct1sqna.pdf) that the OF TYPE clause can use..<br= > <ul>OF TYPE rowtype ( field-definition-comma-list , multi-column-constr= aint-comma-list ) UNDER supertable</ul> It doesn't say that the field-definition-comma-list (my name for a non-= empty comma-separated list of 'field definitions') can be empty, nor th= at the constraint list can be empty, which i

table inheritance question

Related threads

Your privacy choices