Delete Query not working

[mosh]

Hello All,

when I try and run the following query it says "please specify the table", and for some reason in the clause I cannot change the expression WHERE to FROM. Any ideas?

DELETE DISTINCTROW tbl_Table1.Date, tbl_Table1.Time, tbl_Table1.[SRC Extn], tbl_Table1.CLI2, tbl_Table1.[Dest Extn], tbl_Table1.Digits, tbl_Table1.Duration, tbl_ExcludeCLI.ExcludeCLI

FROM tbl_ExcludeCLI LEFT JOIN tbl_Table1 ON tbl_ExcludeCLI.ExcludeCLI = tbl_Table1.CLI2;
________
Jayda_Diamonde

 

[Jon K]

DELETE DISTINCTROW
tbl_Table1.Date,
tbl_Table1.Time,
tbl_Table1.[SRC Extn],
tbl_Table1.CLI2,
tbl_Table1.[Dest Extn],
tbl_Table1.Digits,
tbl_Table1.Duration,
tbl_ExcludeCLI.ExcludeCLI

FROM tbl_ExcludeCLI LEFT JOIN tbl_Table1 ON
tbl_ExcludeCLI.ExcludeCLI = tbl_Table1.CLI2;

It's unclear what you want your query to delete. In a delete query, you can delete records from ONE table only, not from more than one table.


The follwoing two queries will work, though they may not be exactly what you wanted.

DELETE DISTINCTROW tbl_Table1.*
FROM tbl_ExcludeCLI LEFT JOIN tbl_Table1 ON tbl_ExcludeCLI.ExcludeCLI=tbl_Table1.CLI2;

DELETE DISTINCTROW tbl_ExcludeCLI.*
FROM tbl_ExcludeCLI LEFT JOIN tbl_Table1 ON tbl_ExcludeCLI.ExcludeCLI=tbl_Table1.CLI2;


And so will the following query, which deletes those records from table tbl_ExcludeCLI whose ExcludeCLI values do not exist in CLI2 in table tbl_Table1.

DELETE DISTINCTROW tbl_ExcludeCLI.*
FROM tbl_ExcludeCLI LEFT JOIN tbl_Table1 ON tbl_ExcludeCLI.ExcludeCLI=tbl_Table1.CLI2
WHERE tbl_Table1.CLI2 Is Null
.