SQL DELETE from multiple tables

• wern

  SSC Enthusiast

  Points: 101

  More actions

  September 23, 2003 at 5:53 pm

  #176723

  Hello ,

  the sql will not delete from the tables if more then 2 rows have to be deleted... can sombody please have a look and guide me in the right direction whow can i make it to delete all entries from the secondary table no matter 1 or 20 entries.

  y = Request.QueryString("y")

  set DoDelete = Server.CreateObject("ADODB.Command")

  DoDelete.ActiveConnection = "kratistos"

  DoDelete.CommandText = "DELETE Quotes.*, QuoteDetails.* FROM Quotes LEFT JOIN QuoteDetails ON Quotes.QuoteID = QuoteDetails.QuoteDID WHERE (((Quotes.QuoteID)="& y &"))"

  DoDelete.CommandType = 1

  DoDelete.CommandTimeout = 0

  DoDelete.Prepared = true

  DoDelete.Execute()

  Response.Redirect("Quotes_quick_look.asp")

• scking

  SSCrazy

  Points: 2598

  More actions

  September 25, 2003 at 9:06 am

  #475830

  Assuming y is a numeric variable and you do NOT have a field in the QuoteDetails table name QuoteDID, you may have erred by using Quotes.QuoteID = QuoteDetails.QuoteDID. If my assumption is not correct and you have a field named QuoteDID in the QuoteDetails table, you could try the following:

  1. Use the Access query designer to build and test your SQL, then paste the content of the SQL view into your script.

  2) Create two separate transactions; one to delete the details and one to delete the quote.

  Steve King