A problem with Delete Query

[mouk2007]

I have set up a Query formed of two tables. However when I run the delete query I get a message saying 'Specify the table containing the record you want to delete'. Could anyone please help me on how to do this. Here is the SQL:

DELETE DISTINCTROW DVDCopy.CopyNumber, DVDCopy.DVDNumber, DVDCopy.DatePurchased, Loan.DateReturned
FROM DVDCopy INNER JOIN Loan ON DVDCopy.CopyNumber = Loan.CopyNumber
WHERE (((DVDCopy.DatePurchased)<CDate(Date())-365) AND ((Loan.DateReturned) Is Not Null));

Many Thanks

 

[djj55]

You can only delete from one table at a time. Your use of Loan. in the delete distinctrow is the problem.

Try:

DELETE * 
FROM DVDCopy 
LEFT JOIN Loan 
ON DVDCopy.CopyNumber = Loan.CopyNumber
WHERE (((DVDCopy.DatePurchased)<CDate(Date())-365) AND ((Loan.DateReturned) Is Not Null));

Good Luck,
djj

 

[mouk2007]

Thanks for your reply. However when I pasted the SQL code you provided I still get the same message:'Specify the table containing the record you want to delete'.

 

[djj55]

You may need: DELETE DVDCopy.*

 

[lespaul]

you can't specify fields in a DELETE statement, a DELETE deletes the ENTIRE record!

Leslie

Have you met Hardy Heron?

 

[PHV]

Leslie, you can in JetSQL ...

 

[lespaul]

really? what does it do? just clear the information from the specified fields?


Leslie

Have you met Hardy Heron?

 

[djj55]

If you are trying to delete a column of data use

UPDATE tablename SET columnname = null;

This can be done in design view but it is easier to show the code.
djj

 

[Golom]

Just to clarify

UPDATE tablename SET columnname = null;

does not delete a column. It sets the values in the column to NULL but the column is not deleted ... it still exists.

 

[djj55]

Thank Golom I should have explained that.
djj