How to indicate which table's records get deleted in a 2-table query.

[MLH]

I had a table (tblReturnRecei pts) with 47 records and I wanted 18
of its records deleted. So I thought I would be smart and make
another table (a temporary junk table) named BadRRIDs and use
it in the following Select query...

SELECT tblReturnReceip ts.RtnRcptID, tblReturnReceip ts.CorrespID,
tblReturnReceip ts.DateSigned, tblReturnReceip ts.OLA,
tblReturnReceip ts.OLA_ID, tblReturnReceip ts.RRTDstamp,
tblReturnReceip ts.RRuser, tblReturnReceip ts.Tracked FROM BadRRIDs
INNER JOIN tblReturnReceip ts ON BadRRIDs.RRID =
tblReturnReceip ts.RtnRcptID;

I wanted to run the query, which lists all the records in
tblReturnReceip ts targeted for deletion, select all the rows
with the mouse and press the delete key. For the life of me,
I can only delete the 18 records in the trash table - BadRRIDs.

How do I tell the query to delete the records in the OTHER table?

[Tom van Stiphout]

On Fri, 17 Mar 2006 14:52:58 -0500, MLH <CR**@NorthStat e.net> wrote:

Delete * from MainTable
where ID in (select ID from BadTable)

-Tom.

I had a table (tblReturnRecei pts) with 47 records and I wanted 18
of its records deleted. So I thought I would be smart and make
another table (a temporary junk table) named BadRRIDs and use
it in the following Select query...

SELECT tblReturnReceip ts.RtnRcptID, tblReturnReceip ts.CorrespID,
tblReturnRecei pts.DateSigned, tblReturnReceip ts.OLA,
tblReturnRecei pts.OLA_ID, tblReturnReceip ts.RRTDstamp,
tblReturnRecei pts.RRuser, tblReturnReceip ts.Tracked FROM BadRRIDs
INNER JOIN tblReturnReceip ts ON BadRRIDs.RRID =
tblReturnRecei pts.RtnRcptID;

I wanted to run the query, which lists all the records in
tblReturnRecei pts targeted for deletion, select all the rows
with the mouse and press the delete key. For the life of me,
I can only delete the 18 records in the trash table - BadRRIDs.

How do I tell the query to delete the records in the OTHER table?

[MLH]

Thx, Tom.

DELETE tblReturnReceip ts.RtnRcptID, *
FROM tblReturnReceip ts
WHERE tblReturnReceip ts.RtnRcptID In (select RRID from BadRRIDs);

worked perfectly.