How do I write a delete statement in Access SQL?

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

The following will work in T-SQL:

DELETE FROM orders
FROM orders o INNER JOIN orderdetail od ON o.orderid=od.orderid
WHERE od.billitem='a2220'


But I get an error referencing the FROM clause when I try it in Acesss.

How do I write a DELETE in Access that references more than one table?
 
DELETE tblOrders.*
FROM tblOrders
INNER JOIN tblOrderDetail ON tblOrders.OrderId = tblOrderDetail.OrderId
WHERE tblOrderDetail.BillItem = "a2220"

Or use the query designer to build the query and then change the query type
to "Delete Query"
 
Bill's code will work, but here's why you got the error.

Access allows aliasing fields or tables, but you have to
use the AS keyword.

DELETE FROM orders
FROM orders AS o INNER JOIN orderdetail AS od ON
o.orderid=od.orderid
WHERE od.billitem='a2220'

Chris Nebinger
 
Thanks guys for your help

Dave

Bill Edwards said:
DELETE tblOrders.*
FROM tblOrders
INNER JOIN tblOrderDetail ON tblOrders.OrderId = tblOrderDetail.OrderId
WHERE tblOrderDetail.BillItem = "a2220"

Or use the query designer to build the query and then change the query type
to "Delete Query"
Click to expand...
 
Bah, sticky fingers......


Delete * from Orders as o inner join orderdetail as od on
o.orderid=od.orderid where od.billitem='a2220'


Or:

Delete * from orders where orderid in (Select OrderId from
orderdetail where billitem='a2220')


Chris Nebinger
 
Back
Top