Delete From table where exists (select...);

[jckokko]

The table have total 17547 records.

Below query returns 1980 records:

SELECT DISTINCT [mytable].[SUB NO], [mytable].TAX, [mytable].DATE, [mytable_1].TAX, [mytable_1].DATE
FROM [mytable] INNER JOIN [mytable] AS [mytable_1] ON [mytable].[SUB NO] = [mytable_1].[SUB NO]
WHERE ((([mytable].TAX)="0") AND (([mytable].DATE)="25-JUL-07") AND (([mytable_1].TAX)="0") AND (([mytable_1].DATE)="25-JUN-07"));

But,
DELETE FROM mytable
where exists (above select query);
returns 17547 records.

Why?

 

[PHV]

What is the PrimaryKey of mytable and which records you really want to delete ?

FYI, the Exists (above select query) is ALWAYS true ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886