delete query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi,

I have the following:

DELETE *
FROM tbl_masterpop_main
WHERE [loan acct #] in
(SELECT DISTINCT Tbl_archive.[Loan Acct #], Tbl_archive.pasted,
Tbl_archive.PopEnterDt FROM Tbl_archive WHERE (((Tbl_archive.pasted) In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR','CURRENT PAID','CURRENT
BKAMD/DEF','REPO','BK')) AND ((Tbl_archive.PopEnterDt)=Date())));


i want to be able to delete records from tbl_masterpop_main those records
which have a [loan acct #] that exist in tbl_archive with today's date. i
accidentally erased all records from tbl_masterpop_main, so I really need to
know how to amend this query so that the correct records are deleted.

thanks in advance,
geebee
 
Your posted subquery returns more than one field- which should give you a
syntax error.

Does this subquery return the right records to delete?

SELECT Tbl_archive.[Loan Acct #]
FROM Tbl_archive
WHERE Tbl_archive.pasted In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR',
'CURRENT PAID','CURRENT BKAMD/DEF','REPO','BK')
AND Tbl_archive.PopEnterDt=Date()

Do you want to delete all the records from tbl_masterpop_main that have one
of the Loan Acct # or are there other criteria that should be used - such as
a date field in tbl_masterpop_main?
 
i want to delete the records from tbl_masterpop_main, but only if the [loan
acct #] also exists in tbl_archive and if the [popenterdt] = date()

thanks in advance,
geebee


John Spencer said:
Your posted subquery returns more than one field- which should give you a
syntax error.

Does this subquery return the right records to delete?

SELECT Tbl_archive.[Loan Acct #]
FROM Tbl_archive
WHERE Tbl_archive.pasted In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR',
'CURRENT PAID','CURRENT BKAMD/DEF','REPO','BK')
AND Tbl_archive.PopEnterDt=Date()

Do you want to delete all the records from tbl_masterpop_main that have one
of the Loan Acct # or are there other criteria that should be used - such as
a date field in tbl_masterpop_main?

geebee said:
hi,

I have the following:

DELETE *
FROM tbl_masterpop_main
WHERE [loan acct #] in
(SELECT DISTINCT Tbl_archive.[Loan Acct #], Tbl_archive.pasted,
Tbl_archive.PopEnterDt FROM Tbl_archive WHERE (((Tbl_archive.pasted) In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR','CURRENT PAID','CURRENT
BKAMD/DEF','REPO','BK')) AND ((Tbl_archive.PopEnterDt)=Date())));


i want to be able to delete records from tbl_masterpop_main those records
which have a [loan acct #] that exist in tbl_archive with today's date. i
accidentally erased all records from tbl_masterpop_main, so I really need
to
know how to amend this query so that the correct records are deleted.

thanks in advance,
geebee
Click to expand...
Click to expand...
 
oops. yes, your first query returns the right rcords to delete. now how do
i get this query to delete from tbl_masterpop_main those same records which
have a [loan acct #]s of the first query, and with [popenterdt] = date()

thanks in advance,
geebee


John Spencer said:
Your posted subquery returns more than one field- which should give you a
syntax error.

Does this subquery return the right records to delete?

SELECT Tbl_archive.[Loan Acct #]
FROM Tbl_archive
WHERE Tbl_archive.pasted In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR',
'CURRENT PAID','CURRENT BKAMD/DEF','REPO','BK')
AND Tbl_archive.PopEnterDt=Date()

Do you want to delete all the records from tbl_masterpop_main that have one
of the Loan Acct # or are there other criteria that should be used - such as
a date field in tbl_masterpop_main?

geebee said:
hi,

I have the following:

DELETE *
FROM tbl_masterpop_main
WHERE [loan acct #] in
(SELECT DISTINCT Tbl_archive.[Loan Acct #], Tbl_archive.pasted,
Tbl_archive.PopEnterDt FROM Tbl_archive WHERE (((Tbl_archive.pasted) In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR','CURRENT PAID','CURRENT
BKAMD/DEF','REPO','BK')) AND ((Tbl_archive.PopEnterDt)=Date())));


i want to be able to delete records from tbl_masterpop_main those records
which have a [loan acct #] that exist in tbl_archive with today's date. i
accidentally erased all records from tbl_masterpop_main, so I really need
to
know how to amend this query so that the correct records are deleted.

thanks in advance,
geebee
Click to expand...
Click to expand...
 
So is PopEnterDt in the main query also?

DELETE DistinctRow tbl_masterpop_main.[Loan Acct #]
FROM tbl_masterpop_main
WHERE [loan acct #] in
(SELECT Tbl_archive.[Loan Acct #]
FROM Tbl_archive
WHERE Tbl_archive.pasted In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR',
'CURRENT PAID','CURRENT BKAMD/DEF','REPO','BK')
AND Tbl_archive.PopEnterDt=Date())

If tbl_masterpop_main has a field popenterdt and you only want to delete
records that have today's date in addition to the loan Acct # being in
tbl_archive then you need to add the following to the end of the query (not
as part of the subquery)

AND tbl_masterpop_main.[popenterdt] = date()



geebee said:
oops. yes, your first query returns the right rcords to delete. now how
do
i get this query to delete from tbl_masterpop_main those same records
which
have a [loan acct #]s of the first query, and with [popenterdt] = date()

thanks in advance,
geebee


John Spencer said:
Your posted subquery returns more than one field- which should give you a
syntax error.

Does this subquery return the right records to delete?

SELECT Tbl_archive.[Loan Acct #]
FROM Tbl_archive
WHERE Tbl_archive.pasted In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR',
'CURRENT PAID','CURRENT BKAMD/DEF','REPO','BK')
AND Tbl_archive.PopEnterDt=Date()

Do you want to delete all the records from tbl_masterpop_main that have
one
of the Loan Acct # or are there other criteria that should be used - such
as
a date field in tbl_masterpop_main?

geebee said:
hi,

I have the following:

DELETE *
FROM tbl_masterpop_main
WHERE [loan acct #] in
(SELECT DISTINCT Tbl_archive.[Loan Acct #], Tbl_archive.pasted,
Tbl_archive.PopEnterDt FROM Tbl_archive WHERE (((Tbl_archive.pasted) In
('CHARGEOFF','PAIDOFF','CURRENT AMD/DEFR','CURRENT PAID','CURRENT
BKAMD/DEF','REPO','BK')) AND ((Tbl_archive.PopEnterDt)=Date())));


i want to be able to delete records from tbl_masterpop_main those
records
which have a [loan acct #] that exist in tbl_archive with today's date.
i
accidentally erased all records from tbl_masterpop_main, so I really
need
to
know how to amend this query so that the correct records are deleted.

thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
Back
Top