Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Location
    London
    Posts
    73

    Unanswered: Deleting Records from a table

    Hi

    I'm trying to deleterecords from one table. In the query there are 2 tables both joined. I get an error message come on saying

    "Specify The Table Containing The Records you want to Delete"

    Below is the query in SQL

    DELETE tblStockRequired.StockCode, tblStockRequired.StockQTY, tblStockRequired.OrderNumber
    FROM tblOrderCompleted INNER JOIN tblStockRequired ON (tblOrderCompleted.OrderNumber = tblStockRequired.OrderNumber) AND (tblOrderCompleted.StockCode = tblStockRequired.StockCode) AND (tblOrderCompleted.QTY = tblStockRequired.StockQTY);

    I want ot delete all records from tblStockRequired which match up with tblOrderCompleted.

    Thanks in Advanced

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can use a delete query with a join, however the tbalke defitnion and order is critical

    Im guessing you need to change the join to refer to tblStockRequired in place of tblOrderCompleted

    DELETE tblStockRequired.StockCode, tblStockRequired.StockQTY, tblStockRequired.OrderNumber
    FROM tblOrderCompleted INNER JOIN tblStockRequired ON (tblOrderCompleted.OrderNumber = tblStockRequired.OrderNumber) AND (tblOrderCompleted.StockCode = tblStockRequired.StockCode) AND (tblOrderCompleted.QTY = tblStockRequired.StockQTY);

    In effect the JET query analyser chickes out and doesn't try to work which table is actually being referred. Incidently it depends on the SQL but some theorists say its delete * from tblStockRequired......
    ANSI SQL uses another form
    delete from tblStockRequired......

  3. #3
    Join Date
    Jan 2002
    Location
    London
    Posts
    73
    Hi i just tried this and it works

    Thanks for your help

    DELETE tblStockRequired.*
    FROM tblStockRequired
    WHERE OrderNumber & " " & StockCode & " " & StockQTY IN (SELECT OrderNumber & " " & StockCode & " " & QTY FROM tblOrderCompleted);

Posting Permissions

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