Delete parents with no kids

  • Thread starter Thread starter Kevin Witty
  • Start date Start date
K

Kevin Witty

I'd swear I've done this before, but every time I try now I get the error
message "Could not delete from specified tables". The SQL (among others
I've tried) is

"DELETE tInvoices.*, tInvoicedOrders.InvoiceKey
FROM tInvoices LEFT JOIN tInvoicedOrders ON tInvoices.InvKey =
tInvoicedOrders.InvoiceKey
WHERE (((tInvoicedOrders.InvoiceKey) Is Null));"

What am I doing wrong? No one else is in the table I'm trying to delete
from, and the db is not read-only, which is all Help suggests. (I'm trying
to delete tInvoice records which have no matching tInvoicedOrders record.)

Thanks,

Kevin
 
Isn't this Subject line carrying Family Values entirely too far!? said:
I'd swear I've done this before, but every time I try now I get the error
message "Could not delete from specified tables". The SQL (among others
I've tried) is

"DELETE tInvoices.*, tInvoicedOrders.InvoiceKey
FROM tInvoices LEFT JOIN tInvoicedOrders ON tInvoices.InvKey =
tInvoicedOrders.InvoiceKey
WHERE (((tInvoicedOrders.InvoiceKey) Is Null));"

What am I doing wrong?
Click to expand...

The DELETE clause as built by the query grid keeps both tables'
fields. You need the tInvoiceOrders.InvoiceKey in the WHERE clause but
it should not be in the DELETE clause - either remove it, leaving just

DELETE tInvoices.*
FROM tInvoices... <the rest is ok>

or (equivalently) uncheck the Show checkbox in the query grid.
 
Well, actually, parents with no kids used to be a good thing, but apparently
now we're getting short on keeping us old farts provided with WhatEverCare.
Uuhh, nevermind.

Nice to get answers back, but not nice to get none that work. Honestly,
folks, I've tried every combination of SQL that's been suggested, but none
works. Really, <the rest is okay> isn't. Must be something else going
wrong here.

From SQL view:

DELETE tInvoices.*
FROM tInvoices LEFT JOIN tInvoicedOrders ON tInvoices.InvKey =
tInvoicedOrders.InvoiceKey
WHERE ((([tInvoicedOrders].[InvoiceKey]) Is Null));

Gives me the same old error message: "Cannot delete from specified tables".

Sorry,

Kevin

(Lord, John, I answered questions for 10 years on the DE forum, but you've
now done it longer than I did,,, my compliments to you!)



Isn't this Subject line carrying Family Values entirely too far!? said:
I'd swear I've done this before, but every time I try now I get the error
message "Could not delete from specified tables". The SQL (among others
I've tried) is

"DELETE tInvoices.*, tInvoicedOrders.InvoiceKey
FROM tInvoices LEFT JOIN tInvoicedOrders ON tInvoices.InvKey =
tInvoicedOrders.InvoiceKey
WHERE (((tInvoicedOrders.InvoiceKey) Is Null));"

What am I doing wrong?
Click to expand...

The DELETE clause as built by the query grid keeps both tables'
fields. You need the tInvoiceOrders.InvoiceKey in the WHERE clause but
it should not be in the DELETE clause - either remove it, leaving just

DELETE tInvoices.*
FROM tInvoices... <the rest is ok>

or (equivalently) uncheck the Show checkbox in the query grid.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access
 
From SQL view:

DELETE tInvoices.*
FROM tInvoices LEFT JOIN tInvoicedOrders ON tInvoices.InvKey =
tInvoicedOrders.InvoiceKey
WHERE ((([tInvoicedOrders].[InvoiceKey]) Is Null));

Gives me the same old error message: "Cannot delete from specified tables".
Click to expand...

Hrm. This should work if there is a unique Index on InvKey and a
relationship. If it doesn't, try a (much slower) Not Exists clause:

DELETE tInvoices.* FROM tInvoices
WHERE NOT EXISTS
(SELECT InvoiceKey FROM tInvoicedOrders WHERE
tInvoicedOrders.InvoiceKey = tInvoices.InvKey);
 
I don't know if you've tried this, but I would get rid of the "tInvoices.*" part. Just make i

"DELETE FROM...

That's the syntax I always use for my deletes. Your join syntax looks OK

----- Kevin Witty wrote: ----

Well, actually, parents with no kids used to be a good thing, but apparentl
now we're getting short on keeping us old farts provided with WhatEverCare
Uuhh, nevermind

Nice to get answers back, but not nice to get none that work. Honestly
folks, I've tried every combination of SQL that's been suggested, but non
works. Really, <the rest is okay> isn't. Must be something else goin
wrong here

From SQL view

DELETE tInvoices.
FROM tInvoices LEFT JOIN tInvoicedOrders ON tInvoices.InvKey
tInvoicedOrders.InvoiceKe
WHERE ((([tInvoicedOrders].[InvoiceKey]) Is Null))

Gives me the same old error message: "Cannot delete from specified tables"

Sorry

Kevi

(Lord, John, I answered questions for 10 years on the DE forum, but you'v
now done it longer than I did,,, my compliments to you!



On Wed, 28 Apr 2004 19:50:08 GMT, "Kevin Witty" <[email protected]
wrote


Isn't this Subject line carrying Family Values entirely too far!? said:
message "Could not delete from specified tables". The SQL (among other
I've tried) i
FROM tInvoices LEFT JOIN tInvoicedOrders ON tInvoices.InvKey
tInvoicedOrders.InvoiceKe
WHERE (((tInvoicedOrders.InvoiceKey) Is Null));
Click to expand...

The DELETE clause as built by the query grid keeps both tables
fields. You need the tInvoiceOrders.InvoiceKey in the WHERE clause bu
it should not be in the DELETE clause - either remove it, leaving jus

DELETE tInvoices.
FROM tInvoices... <the rest is ok

or (equivalently) uncheck the Show checkbox in the query grid

John W. Vinson[MVP
Come for live chats every Tuesday and Thursda
http://go.compuserve.com/msdevapps?loc=us&acces
 
DELETE tInvoices.* FROM tInvoices
WHERE NOT EXISTS
(SELECT InvoiceKey FROM tInvoicedOrders WHERE
tInvoicedOrders.InvoiceKey = tInvoices.InvKey);

worked a treat. Go figure, and thanks!
 
Back
Top