Delete Query

Results 1 to 2 of 2

Thread: Delete Query

  1. #1
    Join Date
    Dec 1969

    Default Delete Query

    Hi I am having trouble with some SQl to delete records.<BR><BR>I have a Table created by a download program tblFSO_0 that does not have a Unique Key. I need to update a second identically structured Table, tblFSO_1 by deleting any records in tblFSO_1 that are not in tblFSO_0, then updating records in tblFSO_1 from tblFSO_0 that match and then appending new records from tblFSO_0 to tblFSO_1.<BR><BR>The Update and Append work fine but I can&#039;t construct a Delete that will run, I thought that this should work but it doesn&#039;t and I don&#039;t know why not:<BR><BR>stSQL="DELETE tblFSO_1.* FROM tblFSO_1 LEFT JOIN tblFSO_0 ON<BR> (tblFSO_1.SCOM=tblFSO_0.SCOM) AND<BR> (tblFSO_1.SORD=tblFSO_0.SORD)<BR> WHERE ((tblFSO_0.SCOM) Is Null) AND<BR> ((tblFSO_0.SORD) Is Null);<BR><BR>Joins to SCOM and SORD provide the Unique Key.<BR><BR>Thanks for any advice,<BR><BR>Doug

  2. #2
    Join Date
    Dec 1969

    Default RE: Delete Query

    You cannot do a join in a delete statement!<BR><BR>you have to make a list of the ones you want<BR><BR>DELETE from tblFSO_1 Where SCOM in (Select tblFSO.SCOM FROM tblFSO_1 LEFT JOIN tblFSO_0 ON<BR>(tblFSO_1.SCOM=tblFSO_0.SCOM) AND<BR>(tblFSO_1.SORD=tblFSO_0.SORD)<BR>WHERE ((tblFSO_0.SCOM) Is Null) AND<BR>((tblFSO_0.SORD) Is Null));<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts