Delete from multiple tables

Results 1 to 3 of 3

Thread: Delete from multiple tables

  1. #1
    Join Date
    Dec 1969

    Default Delete from multiple tables

    I am trying to delete entries from two tables at the same time, the code below seems to delete the Recommendations table but not the Pages, but when Recommendations is empty it deletes the Pages okay. I am using SQL Server...<BR><BR>strsql = "DELETE FROM Pages WHERE pageID = " & idtodel<BR>strsql2 = "DELETE FROM Recommendations WHERE pageID LIKE " & idtodel<BR><BR>Set RS = Conn.Execute(strsql2)<BR>Set RS = Conn.Execute(strsql)<BR><BR>Cheers<BR><BR>gary<BR>

  2. #2
    Join Date
    Dec 1969

    Default Relationship between tables?

    If you have defined a FOREIGN KEY relationship between the two tables, it will stop you from deleting Recommendations that have pages.<BR><BR>Try switching the order of your deletes.

  3. #3
    Join Date
    Dec 1969

    Default Three Notes

    Three notes<BR><BR>Note One<BR>It is always good practice to wrap multiple deletes in a transaction so your data remains intact if one of the deltes sufferes a failure.<BR><BR>Note Two<BR>I assume the keys are numeric in value so do not use the LIKE clause in your second query.<BR><BR>Note Three<BR>No recordset is returned from your query so there is no reason to set oRs equal to the result. Also, you could check the Error collection in your Connection object for errors - that should tell you why it is failing.

Posting Permissions

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