Cannot delete from outer join

[Guest]

I'm trying to delete from a select query. The query includes two tables, a
parent and child table. The query looks like the following:

SELECT PMOC.PMOCID, PSSR.PMOCID
FROM PMOC LEFT JOIN PSSR ON PMOC.PMOCID = PSSR.PMOCID;

Based on a posting I also tried the following:

SELECT DISTINCTROW PMOC.PMOCID, PSSR.PMOCID
FROM PMOC LEFT JOIN PSSR ON PMOC.PMOCID = PSSR.PMOCID;

The record from the child table (PSSR) is deleted, however, the parent
(PMOC) record is not deleted. In the relationship I have it defined as
cascade delete.

When I try the delete it appears to work. The record is even removed from
the query display. However, if I exit and reentry the query the parent
record is back.

Does someone know what is happening?

Thanks,
Leif

 

[Tom Ellison]

Dear Leif:

First, deleting from a child table will never result in the deletion of the
parent row. If you have cascade deletion set up, deleting the parent is
what is required. So, perhaps you want to just delete certain parent rows
and have the child rows deleted from that.

If it is essential to qualify which parent row(s) to delete by looking at
the child rows, then this may be possible as well. Please post what query
work you have so far, and describe the criteria for the deletion. Perhaps
we can figure out something from that.

Tom Ellison

 

[Guest]

Thanks, Tom, for your reply.

The query is the same as I showed below, repeated again:

SELECT PMOC.PMOCID, PSSR.PMOCID
FROM PMOC LEFT JOIN PSSR ON PMOC.PMOCID = PSSR.PMOCID;

PMOCID is the primary key of the PMOC table. PMOCID in the PSSR table is
the foreign key.

This is only part of the fields of a record source of a bound form.
However, that doesn't really matter, since I get the same result from the
form or just executing the query directly.

The parent table is PMOC, the child table is PSSR. As you can see I'm
joining them in a manner so that I see ALL PMOC records (parent), and the
child records, if they exist, in PSSR.

After executing the query if I delete a parent record that has no child it
*APPEARS* in the query to be deleted (the row is removed from the query, no
error message). However, if I exit the query and reenter (or look directly
at the table) I found the parent record was not delete.

If I delete a parent record that has a child, again it *APPEARS* that the
record is deleted, however, if I exit the query and reenter I found only the
child record was deleted, the parent record still exists.

Do you see anything wrong with this query?

 

[peregenem]

PMOCID is the primary key of the PMOC table. PMOCID in the PSSR table is
the foreign key.

If I delete a parent record that has a child, again it *APPEARS* that the
record is deleted, however, if I exit the query and reenter I found only the
child record was deleted, the parent record still exists.

Do you see anything wrong with this query?

You are trying to do two DELETE operations in one.

If you want to do it in one operation, do as Tom Ellison suggested and
use an ON DELETE CASCADE. If this is not possible (e.g. each table is
in a separate database), use two DELETE operations

DELETE
FROM PSSR
WHERE <expression>

followed by

DELETE
FROM PMOC
WHERE <expression>

To deleted all orphaned rows (FOREIGN KEY should prevent this from
arising, though), you could use

DELETE
FROM PSSR
WHERE NOT EXISTS (
SELECT *
FROM PMOC
WHERE PMOC.PMOCID = PSSR.PMOCID)

 

[Guest]

Thanks for your response.

I currently have the relationship set up as a cascade delete, but I still
have the problem of the parent not being deleted. It will delete if I set up
the join as an inner join, but I need to see parent records without child
records, so it needs to be an outer join.

I'm coming to the conclusion, as you suggest, that I need to set this up as
two separate queries. That's probably how I should have designed it in the
beginning. On my project form it is a subform PMOC/PSSR. I think I'll
change that to three levels. My project form, the PMOC subform of the
project form, and the PSSR subform of the PMOC subform. I think that makes
the most sense. My other alternative is to add coding to programmatically
delete the parent record.

Regards,
Leif

 

[peregenem]

I currently have the relationship set up as a cascade delete, but I still
have the problem of the parent not being deleted.

You are deleting from the wrong table. Just delete the 'parent' and the
ON DELETE CASCADE action will remove the 'children'. This is what the
syntax was designed to do.

FWIW the correct terms in declarative referential integrity (DRI) are
'referenced table' (parent) and 'referencing table' (child)
respectively; or if you are simply referring to the two tables in an
OUTER JOIN, they are the 'preserved table' (parent) and 'unpreserved
table' (child) respectively. 'Parent' and 'child' allude to a
hierarchy; the relational model requires a different mindset.

 

[Guest]

Perhaps you can tell me how I delete the parent, 'preserved table' , from an
outer join query.

 

[John Vinson]

Perhaps you can tell me how I delete the parent, 'preserved table' , from an
outer join query.

Use its name in the Delete clause:

DELETE [preserved table].*
FROM [preserved table] LEFT JOIN [unpreserved table]
ON [preserved table].[joinfield] = [unpreserved table].[joinfield]
WHERE <criteria>

Of course if you don't provide criteria, you'll delete all records
from both tables; and if you provide criteria (other than Joinfield IS
NULL) on the unpreserved table, you might as well use an Inner Join.

John W. Vinson[MVP]

 

[Guest]

John,

Thats fine, but that is not what I'm asking, what I have here is a
**SELECT** query. On a select query I'm normally able to right click on a
row (in datasheet view), choose delete record, and its gone. This select
query is the record source of my form. I want to delete the parent and child
from the form.

This works fine on a single table or an inner joined table. The problem
here is I have an outer join table.

It's OK to tell me it cannot be done. That seems to be the case, but no one
tells me that. If I have to write VBA code to fire a delete statement thats
fine.

Someone please tell it cannot be done, or if I'm doing the SELECT wrong
please let me know.

Thanks.

Perhaps you can tell me how I delete the parent, 'preserved table' , from an
outer join query.

Use its name in the Delete clause:

DELETE [preserved table].*
FROM [preserved table] LEFT JOIN [unpreserved table]
ON [preserved table].[joinfield] = [unpreserved table].[joinfield]
WHERE <criteria>

Of course if you don't provide criteria, you'll delete all records
from both tables; and if you provide criteria (other than Joinfield IS
NULL) on the unpreserved table, you might as well use an Inner Join.

John W. Vinson[MVP]

 

[John Vinson]

Thats fine, but that is not what I'm asking, what I have here is a
**SELECT** query. On a select query I'm normally able to right click on a
row (in datasheet view), choose delete record, and its gone. This select
query is the record source of my form. I want to delete the parent and child
from the form.

Sorry... I did misunderstand.

You'll need code, perhaps in the On Delete or AfterDeleteConfirm event
of the form. If you're using a query datasheet all bets are off, you
have no usable events.

As far as I know, a Form based on any Join query will delete records
only from the "many" side table, regardless of join type.

John W. Vinson[MVP]

 

[peregenem]

Thats fine, but that is not what I'm asking, what I have here is a
**SELECT** query.

Use a SELECT query (inner/outer/whatever) to select a resultset to
return data to your application. Use a DELETE command to remove rows
from the referenced table and let the CASCADE action automatically
remove the rows in the referenced table for you. The SELECT query and
the DELETE command are different animals with different syntax needs;
you shouldn't expect to use the same SQL construct for both.