Suggestion nedded !!

Results 1 to 2 of 2

Thread: Suggestion nedded !!

  1. #1
    Join Date
    Dec 1969

    Default Suggestion nedded !!

    I am trying to delete records from multiple tables.<BR>e.g. I have a ticket with ticketno 23456 which I need to delete from the three tables Payouts, Passenger and Invoice.<BR>The table Invoice has 1:m relation with Passenger and Payouts table.<BR>I need to find the invoiceno for the ticket 23456 and check if there are any more tickets for that invoice number. If there aren&#039;t any tickets under that invoiceno I can delete the record from the Invoice table else I can&#039;t.<BR>I have written a code as follows however I am not sure if this is the efficient way..any suggestions???? <BR>&#039;deleteing the ticketno from Payouts table.<BR>Conn.Execute("DELETE FROM Payouts WHERE Ticketno=&#039;"& Request.Form("txtticket") &"&#039;")<BR>&#039;capturing the invoiceno for the ticket<BR>set rsInvoice = "SELECT Invoiceno FROM Passenger Ticketno=&#039;"& Request.Form("txtticket") &"&#039;")<BR>If rsInvoice.EOF then<BR>&#039;the invoiceno can be deleted from Invoice table since there are no any tickets for this invoiceno<BR><BR>Conn.Execute("DELETE FROM Invoice WHERE InvoiceNo="& rsInvoice("InvoiceNo")&"")<BR><BR>End if<BR><BR>Any suggestion would be greatly appreciated. THANKS!

  2. #2
    Join Date
    Dec 1969

    Default You can combine your last two SQL statements

    into one like this:<BR><BR>DELETE FROM Invoice <BR>WHERE InvoiceNo = &#039;101&#039; AND<BR> InvoiceNo NOT IN <BR> (SELECT Invoiceno <BR> FROM Passenger<BR> WHERE Ticketno=&#039;T101&#039;) <BR><BR>or even better you can put all your statements into a stored procedure. Incidentally, your second SQL statement was missing a WHERE clause.<BR><BR>Pete<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