Delete query foible

[Petr Dane¹]

I had a weird problem with a delete query. I load a temp table with some
junk, then move some of the junk that does not have corresponding junk in
another table to yet another table and remove that same junk from the first
temp table. I do with two subsequent queries, an INSERT INTO to copy the
junk, then a DELETE to remove the junk.

Briefly:
1. Load records into TableA
2. Copy records from TableA to TableB, where TableC does not contain a
record matching something from TableA
3. Delete records from TableA, where TableC does not contain a record
matching something from TableA

The copy worked fine, but the delete failed, even though it was written
essentially identically.

The problem turned out to be in the join. My first attempt used a function
in the join, as follows:

INSERT INTO Akces
SELECT AkcesTemp5.*
FROM AkcesTemp5 LEFT JOIN AkcesTemp5P ON
AkcesTemp5.Akcesit=Left(AkcesTemp5P.Akcesit,5)
WHERE AkcesTemp5P.Akcesit IS NULL;

DELETE DISTINCTROW AkcesTemp5.*
FROM AkcesTemp5 LEFT JOIN AkcesTemp5P ON
AkcesTemp5.Akcesit= Left(AkcesTemp5P.Akcesit,5)
WHERE AkcesTemp5P.Akcesit IS NULL;

It turns out that using a function in the join worked fine for the copy but
failed with a 3086 error on the delete. Following a hint about literals in
joins that I found somewhere in the archives, I added a new field that only
has the first five characters of the join field, which is generated during
the initial load of the first temp table, then I join on that, without the
Left function.

INSERT INTO Akces
SELECT AkcesTemp5.*
FROM AkcesTemp5 LEFT JOIN AkcesTemp5P ON
AkcesTemp5.Akcesit= AkcesTemp5P.Akcesit5
WHERE AkcesTemp5P.Akcesit IS NULL;

DELETE DISTINCTROW AkcesTemp5.*
FROM AkcesTemp5 LEFT JOIN AkcesTemp5P ON
AkcesTemp5.Akcesit=AkcesTemp5P.Akcesit5
WHERE AkcesTemp5P.Akcesit IS NULL;

I changed the join in the copy statement as well, since it's simpler and
easier to join on a full field, and I now have that field available, even
though it worked before. I have no idea why a delete statement is so fussy,
while the copy statement worked fine, but this fixed the problem. Hope this
may save someone some hair-pulling.

Pete