Hi!

I have this problem of deleting the related records in a joined table.  for
example, I have this Students table and I have another table named Subjects
table the link between this tables is the StudNum which is the primary key
of Students table.  Now what I want is if I delete a student is that all the
related records in Subjects table must also be deleted.  Below is the code I
used to do this:

DELETE tblStudents.*, tblSubjects.* FROM tblStudents INNER JOIN tblSubjects
ON tblStudents.StudNum = tblSubjects.StudNum WHERE tblStudents.StudNum
1020;

Is my statement wrong?  Could someone correct my statement if I'm wrong.  I
was used only deleting records in only ONE table. :) I have never tried this
before.  Thank you very much.

Thanks/Regards,

Enzo :)

Enzo

I don't think you can join DELETE queries like this - it's one DELETE 
statement per table!

There are two ways to do this:

1) This one only works in Access. When you set up the referential 
integrity between the two tables, tick the 'Cascade Delete Related 
Records' box.

If you do this, when you delete the records from the Students table all 
the related records from the Subjects table will go too.

2) Will work in any SQL Database. Run two delete queries (obviously)! Make 
sure you delete the records from the Subjects table first to prevent 
referential integrity problems

Brian

> Hi!

I have this problem of deleting the related records in a joined table.  for
example, I have this Students table and I have another table named Subjects
table the link between this tables is the StudNum which is the primary key
of Students table.  Now what I want is if I delete a student is that all 
the
related records in Subjects table must also be deleted.  Below is the code 
I
used to do this:

DELETE tblStudents.*, tblSubjects.* FROM tblStudents INNER JOIN tblSubjects
ON tblStudents.StudNum = tblSubjects.StudNum WHERE tblStudents.StudNum
1020;

Is my statement wrong?  Could someone correct my statement if I'm wrong.  I
was used only deleting records in only ONE table. :) I have never tried 
this
before.  Thank you very much.

Thanks/Regards,

Enzo :)

Brian,

    I knew about the database relationship on Access this is why I'm doing
the "JOINED DELETE" because I want to make the "Cascade delete" that Access
has when you setup the referential integrity of the tables and also "Cascade
Update".  Do you mean also in updates I can't make "JOINED UPDATES?  pheewww
:(

    Thanks for letting me know that the DELETE statement is only used PER
table :(  So how I gonna delete (from my Student-Subjects example) the
related records in Subjects table with the X Students in the Student Table.

        DELETE tblSubjects.* FROM tblStudents INNER JOIN tblSubjects ON
tblStudents.StudNum=tblSubject.StudNum WHERE
        tblStudents.DateGraduated < #1/1/200# ;

    With my above statement is my statement correct already?  Is it
possible?

Thanks/Regards,

Enzo :)

----- Original Message -----
From: <brian.skelton@b...>
To: "Access" <access@p...>
Sent: Tuesday, March 26, 2002 12:35 PM
Subject: [access] Re: Deleting Joined Tables


> Enzo
>
> I don't think you can join DELETE queries like this - it's one DELETE
> statement per table!
>
> There are two ways to do this:
>
> 1) This one only works in Access. When you set up the referential
> integrity between the two tables, tick the 'Cascade Delete Related
> Records' box.
>
> If you do this, when you delete the records from the Students table all
> the related records from the Subjects table will go too.
>
> 2) Will work in any SQL Database. Run two delete queries (obviously)! Make
> sure you delete the records from the Subjects table first to prevent
> referential integrity problems
>
> Brian
>
> > Hi!
>
> I have this problem of deleting the related records in a joined table.
for
> example, I have this Students table and I have another table named
Subjects
> table the link between this tables is the StudNum which is the primary key
> of Students table.  Now what I want is if I delete a student is that all
> the
> related records in Subjects table must also be deleted.  Below is the code
> I
> used to do this:
>
> DELETE tblStudents.*, tblSubjects.* FROM tblStudents INNER JOIN
tblSubjects
> ON tblStudents.StudNum = tblSubjects.StudNum WHERE tblStudents.StudNum
> 1020;
>
> Is my statement wrong?  Could someone correct my statement if I'm wrong.
I
> was used only deleting records in only ONE table. :) I have never tried
> this
> before.  Thank you very much.
>
> Thanks/Regards,
>
> Enzo :)
>

Enzo

No, I'm afraid you can't do joined updates either, as a one-to-manynjoined 
query isn't updatable. It makes sense when you think about it, as the ONE 
side of the join will appear multiple times in the resulting recordset, so 
how could you change just a single instance of that data?

The delete statement below looks good. You can use joins and WHERE clauses 
to limit the records you wish to delete, but you can only delete from one 
table at a time

Brian

> Brian,

    I knew about the database relationship on Access this is why I'm doing
the "JOINED DELETE" because I want to make the "Cascade delete" that Access
has when you setup the referential integrity of the tables and 
also "Cascade
Update".  Do you mean also in updates I can't make "JOINED UPDATES?  
pheewww
:(

    Thanks for letting me know that the DELETE statement is only used PER
table :(  So how I gonna delete (from my Student-Subjects example) the
related records in Subjects table with the X Students in the Student Table.

        DELETE tblSubjects.* FROM tblStudents INNER JOIN tblSubjects ON
tblStudents.StudNum=tblSubject.StudNum WHERE
        tblStudents.DateGraduated < #1/1/200# ;

    With my above statement is my statement correct already?  Is it
possible?

Thanks/Regards,

Enzo :)

----- Original Message -----
From: <brian.skelton@b...>
To: "Access" <access@p...>
Sent: Tuesday, March 26, 2002 12:35 PM
Subject: [access] Re: Deleting Joined Tables


> Enzo
>
> I don't think you can join DELETE queries like this - it's one DELETE
> statement per table!
>
> There are two ways to do this:
>
> 1) This one only works in Access. When you set up the referential
> integrity between the two tables, tick the 'Cascade Delete Related
> Records' box.
>
> If you do this, when you delete the records from the Students table all
> the related records from the Subjects table will go too.
>
> 2) Will work in any SQL Database. Run two delete queries (obviously)! 
Make
> sure you delete the records from the Subjects table first to prevent
> referential integrity problems
>
> Brian
>
> > Hi!
>
> I have this problem of deleting the related records in a joined table.
for
> example, I have this Students table and I have another table named
Subjects
> table the link between this tables is the StudNum which is the primary 
key
> of Students table.  Now what I want is if I delete a student is that all
> the
> related records in Subjects table must also be deleted.  Below is the 
code
> I
> used to do this:
>
> DELETE tblStudents.*, tblSubjects.* FROM tblStudents INNER JOIN
tblSubjects
> ON tblStudents.StudNum = tblSubjects.StudNum WHERE tblStudents.StudNum
> 1020;
>
> Is my statement wrong?  Could someone correct my statement if I'm wrong.
I
> was used only deleting records in only ONE table. :) I have never tried
> this
> before.  Thank you very much.
>
> Thanks/Regards,
>
> Enzo :)
>