Delete Query Question

[owens2k]

i am trying to delete some data but am having trouble.

First of all i am appending data in tblCustomers where the field "end of stay" (in a different table (tblBookings)) equals the current date.

Using the same SQL in a new query but changing the query to a delete query, should in theory delete the same records should it not? When i do change it to a delete query i get the following error message

"specify the table containing the records you want to delete"

does anybody know what im doin wrong, or another way round it?

 

[Quehay]

This is SQL that deletes a customer from Northwind based on an order date criterion:

[tt]

delete
FROM customers
WHERE customers.customerid in (SELECT customers.customerid
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
where orders.orderdate=#1/2/97#);
[/tt]

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[lespaul]

is your syntax right?


DELETE FROM TABLENAME WHERE CRITERIA

what is your current SQL?

leslie

 

[owens2k]

Well the SQL is the same as the append query except its a delete query, but i keep gettin an error for some reason.

Here is my SQL

DELETE tblCustomers.*, tblBookings.[End of stay]
FROM tblCustomers INNER JOIN tblBookings ON tblCustomers.[Customer ID] = tblBookings.[Customer ID]
WHERE (((tblBookings.[End of stay])=Date()));

and i get the error "could not delete from specified tables"

the tables are not open nor are they read only!!

 

[Quehay]

Look at the sample above and see how this is similar and different to what you've got, exchanging [orders] for [bookings] and Date() for a date literal (#n/n/nn#)...

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[owens2k]

i used

delete
FROM tblcustomers
WHERE tblcustomers.customer id in (SELECT tblcustomers.customer id
FROM tblCustomers INNER JOIN tblbookings ON tblCustomers.Customer ID = tblbookings.CustomerID
where tblbookings.endofstay=#1/2/97#);

and it says missing syntax.

also wouldnt this only filter records for the that date typed into the SQL and not the current date?

 

[Quehay]

Swap Date() for #1/2/97#);

Try DELETE * FROM, although it shouldn't be necessary.
Try running the nested subquery by itself. Is there really a space in [customer id] ?

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[owens2k]

there is a space in customer ID should i get rid of the space? there are also spaces in the field "end of stay"

i now tried...

DELETE *
FROM tblCustomers
WHERE tblCustomers.Customer ID in (SELECT tblCustomers.Customer ID
FROM tblCustomers INNER JOIN tblBookings ON tblCustomers.Customer ID = tblBookings.Customer ID
WHERE tblBookings.End of stay=Date());

but it still says missing syntax!

 

[Quehay]

Sorry, just checking your spelling (in general you want to avoid spaces in names or you'll continue to ask this question...). You've got two WHERE's--try:
[tt]
DELETE *
FROM tblCustomers
WHERE tblCustomers.Customer ID in (
SELECT tblCustomers.Customer ID
FROM tblCustomers
INNER JOIN tblBookings
ON tblCustomers.Customer ID = tblBookings.Customer ID
AND tblBookings.End of stay=Date());[/tt]



Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[owens2k]

im getting the same error still. Maybe i should rename my fields so that there are no spaces.

Although this may mess up the rest of my database!

 

[Quehay]

Whoah there. Fix one discrete problem at a time or you'll be hating life! There are two queries here. Does

SELECT tblCustomers.Customer ID
FROM tblCustomers
INNER JOIN tblBookings
ON tblCustomers.Customer ID = tblBookings.Customer ID
AND tblBookings.End of stay=Date();

Work independently?



Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[owens2k]

now i get

"syntax error (missing operator) in query expression 'tblCustomers.Customer ID'"

 

[Quehay]

Jeez I'm out to lunch here. It's a pain not being able to just run the damned query for you. I missed syntax in last post--screen blindness. This should work as long as it refers to tables/cols in your db!!
[tt]
DELETE *
FROM tblCustomers
WHERE tblCustomers.Customer ID in (SELECT tblCustomers.Customer ID
FROM tblCustomers INNER JOIN tblBookings ON tblCustomers.Customer ID = tblBookings.Customer ID
WHERE tblBookings.End of stay=Date());[/tt]

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com