Delete Query Problem

[adam.greer]

Hi Guys

I want to delete some fields in my table based on the fields in another table. The two fields have a relationship.

Here is the SQL

DELETE Playlists_OnAir.PlaylistAUID, PlaylistTimes_OnAir.BroadcastTime
FROM Playlists_OnAir INNER JOIN PlaylistTimes_OnAir ON Playlists_OnAir.PlaylistAUID = PlaylistTimes_OnAir.OriginalAUID
WHERE (((PlaylistTimes_OnAir.BroadcastTime)<Date()-"7"));


However this asks me to specify the table I want to delete from, and I can't seem to do that.

Any ideas?

Thanks


Adam

 

[Bobadopolis]

I believe DELETE queries are for deleting entire records from a table. Use another query (append or update) to do what you want to do or else change your approach, for example I just created this DELETE query:

DELETE [B]tblResults.*[/B]
FROM tblResults
WHERE (((tblResults.AudiometryID)=[Forms]![frmAudiometryEdit]![txtAudiometryID]));

HTH

Bobadopolis

 

[adam.greer]

I am trying to delete entire records.

My problem being it won't allow me to delete fields in one table based on a second table.

Any ideas where I am going wrong?

 

[Bobadopolis]

Try just changing the fields selected to an asterix something like:

DELETE Playlists_OnAir.*, [I]blah blah[/I]
FROM Playlists_OnAir [I]blah blah[/I]

This selects ALL the fields.

You could try changing DELETE to SELECT to see what records the query is returning...

 

[workmad3]

Hmm... looking at your query, I think it needs some restructuring.
Possibly try

DELETE FROM PlayLists_OnAir p, PlayListTimes_OnAir pt WHERE (p.BroadcastTime < Date() - 7) AND p.PlayListAUID = pt.OriginalID

You don't need to put a fieldlist in a DELETE.... not sure if this works though, as I generally use cascade options to do multi table deletes rather than delete query.

 

[adam.greer]

No luck with either. The SELECT query brings up all the records without a problem.

Could you explain this cascade option a bit more?

Thanks


Adam

 

[Bobadopolis]

Cascade updating/deleting is part of JETs Referential Integrity (RI). Cascade delete will delete all child records of any parent you delete. You can turn it on in 'Relationships' view. However, I'm not sure that this will be much use to you in this case... I could be wrong(?). Plus, before you turn it on you need to seriously consider if you need it as it could result in irreversible data loss!

Bobadopolis