Re: Delete Query Does Not

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Dear Ron:

It's been years since I did this, but it seems to me that, if you take the
working SELECT query, open it in design view, right click in the empty
portion of the top portion of the windos, select Query Type, and then select
Delete Query, it will transform your SELECT query into an equivalent DELETE
query.

I beleive what it would say is:

DELETE client.*
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730

I'm not sure of this query either, but I'm pretty sure using the Design View
capacities to create it will be reliable. Of course, always make a backup
of your data before running anything I suggest. : )

Or anything else new and uncertain you are just testing for the first time.
Or the second time.

Did I mention before that I'm concerned about the use of Date() in this
query. If you system clock is somehow accidentally set forward to 2/9/2008,
it will delete all your data. What is deleted here depends on the system
clock being set correctly. Having the user enter the desired date might be
better, but that too is subject to mis-typing. The best thing I can think
of at the moment is to rank your data by date and choose a date from that
which assures you the most recent 200, 500, 10000, or whatever number of
records are most recent will not be deleted. Test the date for your
deletions against that. It must be a date prior to your 500 most recent
records.

A good question might be to consider for yourself why it is so necessary to
delete them at all. You could use a similar query mechanism to just
disregard them for reporting or forms purposes, and let them be. This
avoids the possible tragic loss of historic information, especially if your
backup scheme is not highly reliable. If at some future time you want to
change the number of days from 730 to what seems better at that time,
everything would adjust to the new limit immediately. If you set it to 1000
days, you'd instantly have 270 days more information show up (assuming there
is some in your table). If table size (the 2 GB max for Jet files, for
example) or performance is your goal, then archiving them first to a
separate database would be an option. Ask yourself, if all the data you
have accumulated in this table were to disappear due to such a malfunction,
what amount of time and money would it cost? Now are you motivated to
protect it thoroughly?

Well, I've given you the rope to hang yourself. : ) What you do with it
is up to you!

Tom Ellison


"Ron Le Blanc" <rleblanc@xxxxxxxxxxxxx> wrote in message
news:c96dnajKWZQXQnfenZ2dnUVZ_tadnZ2d@xxxxxxxxxx
Dear Tom,

First I thank you for all the help you have given me thus far. This is
a nasty problem!

The last SQL query you gave me (below) runs but gives the following error
message:

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

If I make it a Select query it selects the correct clients!! But a Delete
query give the above error message.

One more tiny tweak maybe?

Thanks and take care...

"Tom Ellison" <tellison@xxxxxxxxxxx> wrote in message
news:%23pCVJbHLGHA.2704@xxxxxxxxxxxxxxxxxxxxxxx
Dear Ron:

Well, I used to be able to write queries. Actually, it's easy enough
when you have the database in front of you.

I think I had the order wrong, didn't I?

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
GROUP BY client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730

I've been on my own problem almost straight for 48 hours. Please
forgive. I'm too tired, or old, or something.

Tom Ellison


"Ron Le Blanc" <rleblanc@xxxxxxxxxxxxx> wrote in message
news:W9WdnUtRZsOXF3TeRVn-qg@xxxxxxxxxx
Dear Tom,

Syntax error in HAVING clause

<Sigh>

Take care,

"Tom Ellison" <tellison@xxxxxxxxxxx> wrote in message
news:%23lm0%23qFLGHA.604@xxxxxxxxxxxxxxxxxxxxxxx
Dear Ron:

Maybe I should make excuses. Not.

OK, this may be what is required:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
HAVING MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

Funny what you forget at times. I THINK this is what is needed.
Please get back, OK? This is sanity check day for me!

Tom Ellison


"Ron Le Blanc" <rleblanc@xxxxxxxxxxxxx> wrote in message
news:NpudnbrnJqiG2nTenZ2dnUVZ_tKdnZ2d@xxxxxxxxxx
The second SQL statement creates an error that says you can't have an
aggregate function in a where clause as in
(MAX(meals.LastVisit)<Date()-730

The meals table contains the client master ID, a meals master ID,
(those two create the combined key for the meals table), a list of
date, one per row, of the dates the client has visited the food bank.
There
can be and often are multiple entries per client. The last row
contains the
last date a client came into the food bank. Soooo... if that date is
two or
more years old I want to delete the client and all records related to
this
client. :)




"Tom Ellison" <tellison@xxxxxxxxxxx> wrote in message
news:usBB3btKGHA.3836@xxxxxxxxxxxxxxxxxxxxxxx
Dear Ron:

I'll make a guess at this. Perhaps we are misunderstanding your
meaning of "LastVisit."

If you have several rows in [meals] each with a different LastVisit,
it is possible you want to delete the client and all meals only when
the most recent LastVisit is prior to 730 days ago. Is that what you
want?

The query as I read it would delete a client and related meals if
there is ANY visit more than 730 days ago. That would be a quite
different thing. It could be:

SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID

I have presented this as a SELECT query not a DELETE. It shows what
rows would be deleted if it were changed to be a DELETE query. By
not deleting anything, testing is easier, as you can change it and
run it again without having to restore the data every time it deletes
wrongly.

To see what you are doing further, build another query around this
(temporarily):

SELECT *
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE client.MasterID IN (
SELECT client.MasterID
FROM client
INNER JOIN meals
ON meals.MasterID = client.MasterID
WHERE MAX(meals.LastVisit) < Date() - 730
GROUP BY client.MasterID)
ORDER BY client.MasterID, meals.LastVisit

Such a test is revealing, is it not?

I would remark that this is a potentially dangerous query. If your
system date is off, you could be removing rows you do not intend.
That sounds like a bit of a risk to me.

Tom Ellison


"Ron Le Blanc" <rleblanc@xxxxxxxxxxxxx> wrote in message
news:P6idnToUh84uU3venZ2dnUVZ_sOdnZ2d@xxxxxxxxxx
This does not work. It selects many clients who have been in within
two years. :(
It does find a bunch of the "right" clients to delete but also
includes what seems be be random other clients not meeting the two
years since last time they came in.

:(


"John Vinson" <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9j0bu19cn0dtigv3mojqbrlddascj5i004@xxxxxxxxxx
On Sat, 4 Feb 2006 23:15:16 -0500, "Ron Le Blanc"
<rleblanc@xxxxxxxxxxxxx> wrote:

DELETE client.*, client.MasterID, meals.LastVisit, client.f_name,
client.l_name
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)<Date()-730));

The error message says: "Cannot delete from table".

You're trying too hard. Don't delete anything from the Meals
table -
cascading deletes will take care of that.

DELETE client.*
FROM client INNER JOIN meals ON client.MasterID = meals.MasterID
WHERE (((meals.LastVisit)<Date()-730));


John W. Vinson[MVP]

















.

Relevant Pages

  • lame server messages in named.log
    ... Mar 30 05:42:30.526 security: info: client 202.52.250.176#1052: ... query (cache) denied ...
    (RedHat)
  • lame server messages in named.log
    ... Mar 30 05:42:30.526 security: info: client 202.52.250.176#1052: ... query (cache) denied ...
    (RedHat)
  • Re: Delete Query Does Not
    ... Tom Ellison ... working SELECT query, open it in design view, right click in the empty ... DELETE client.* ... INNER JOIN meals ...
    (microsoft.public.access.queries)
  • Re: Delete Query Does Not
    ... If I make it a Select query it selects the correct clients!! ... FROM client ... INNER JOIN meals ... The error message says: "Cannot delete from table". ...
    (microsoft.public.access.queries)
  • Re: High CPU in client (Excel, OWC, Proclarity, etc.) accessing Analysis Services
    ... We have encountered similar problems not limited to Excel, but every client. ... occur when there are many dimensions nested on each other on a single axis ... > Performance Guide to optimize the query, the cube, the server, etc. ... > While the query was grinding, the server was doing absolutely nothing. ...
    (microsoft.public.sqlserver.olap)