Re: Question Regarding DELETE FROM ONLY

On Monday 29 May 2006 09:43 am, Michael Fuhr <mike(at)fuhr(dot)org> thus
communicated:
--> On Mon, May 29, 2006 at 08:40:43AM -0400, Terry Lee Tucker wrote:
--> > INSERT INTO ctable (code, name) VALUES ('code_one', 'Code One');
--> > rnd=# SELECT * FROM ptable;
--> > code
--> > ----------
--> > code_one
--> > (1 row)
--> >
--> > rnd=# SELECT * FROM ctable;
--> > code | name
--> > ---------------+----------
--> > code_one | Code One
--> > (1 row)
-->
--> These aren't two distinct records -- they're the same record, the
--> one in ctable, as the following queries show:
-->
--> SELECT tableoid::regclass, * FROM ptable;
--> SELECT tableoid::regclass, * FROM ctable;
-->
--> You can use FROM ONLY to see that the record doesn't actually exist
--> in ptable:
-->
--> SELECT tableoid::regclass, * FROM ONLY ptable;
-->
--> > DELETE FROM ONLY ctable WHERE code ~* 'code_one';
--> >
--> > rnd=# SELECT * FROM ptable;
--> > code
--> > ------
--> > (0 rows)
--> >
--> > The record in ctable AND the record in ptable are both deleted even
though I --> > specified "ONLY ctable" in the delete phrase. Why is this
happening? -->
--> Because there was only one record, the one in ctable, and you deleted
--> it. When you inserted the record into ctable that's the only place
--> it went. The query against ptable showed records in the parent
--> table (none) and records in its child tables (one). After you
--> delete the record from the child the subsequent query against the
--> parent returns zero rows because both tables are now empty (the
--> parent was always empty and the child had its one record deleted).
-->
--> --
--> Michael Fuhr
-->

Thanks for the response Michael. I'm beginning to see the light.