How do I write a delete statement in Access SQL?

[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?

 

[Bill Edwards]

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"

 

[Chris Nebinger]

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

 

[Douglas J. Steele]

I don't believe you can have two FROM keywords in a single query...

 

[Dave]

Thanks guys for your help

Dave

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"

 

[Chris Nebinger]

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