Delete Query

[Guest]

I have the following sql that gives me a " Please Specify the table
containing the records you want to delete" error. I am attempting to delete
records based on the results of another query ( which dumps the results into
a temp table).

DELETE tblSkuList.Key,tblSkuList.Whse,tblSkuList.Sku,tblSkuList.Sku_Nme
tblTempSku.Key FROM tblSkuList LEFT JOIN tblTempSkus ON
tblSkuList.Key=tblTempSkus.Key WHERE (((tblTempSkus.Key) Is Null));

Any ideas why I can't get this to work?

Thanks
Rosco

 

[Dirk Goldgar]

I have the following sql that gives me a " Please Specify the table
containing the records you want to delete" error. I am attempting to
delete records based on the results of another query ( which dumps
the results into a temp table).

DELETE
tblSkuList.Key,tblSkuList.Whse,tblSkuList.Sku,tblSkuList.Sku_Nme
tblTempSku.Key FROM tblSkuList LEFT JOIN tblTempSkus ON
tblSkuList.Key=tblTempSkus.Key WHERE (((tblTempSkus.Key) Is Null));

Any ideas why I can't get this to work?

1. You can only delete from one table at a time, but you include
tblTempSku.Key in the field list.

2. There is no comma delimiting that field from the preceding fields
anyway.

3. Is it "tblTempSku", or "tblTempSkus"? You use both.

If your intention is to delete all the unmatched records from
tblSkuList, try this:

DELETE tblSkuList.*
FROM
tblSkuList
LEFT JOIN
tblTempSkus
ON tblSkuList.Key=tblTempSkus.Key
WHERE tblTempSkus.Key Is Null;

 

[Guest]

1. You can only delete from one table at a time, but you include
tblTempSku.Key in the field list.

2. There is no comma delimiting that field from the preceding fields
anyway.

3. Is it "tblTempSku", or "tblTempSkus"? You use both.

If your intention is to delete all the unmatched records from
tblSkuList, try this:

DELETE tblSkuList.*
FROM
tblSkuList
LEFT JOIN
tblTempSkus
ON tblSkuList.Key=tblTempSkus.Key
WHERE tblTempSkus.Key Is Null;


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks for the response Dirk.
Sorry for the typo it's tblTemp Skus.

I'm now getting a "Could not delete from specified tables" error

Rosco

 

[Dirk Goldgar]

Thanks for the response Dirk.
Sorry for the typo it's tblTemp Skus.

I'm now getting a "Could not delete from specified tables" error

Create a unique index on the field [Key] in tblTempSkus. If you can't
do that, because tblTempSkus has duplicate keys, you can either (a)
remove the duplicates, or (b) try this query:

DELETE tblSkuList.*
FROM tblSkuList
WHERE tblSkuList.Key Not In
(SELECT tblTempSkus.Key FROM tblTempSkus);

 

[Guest]

Thanks for the response Dirk.
Sorry for the typo it's tblTemp Skus.

I'm now getting a "Could not delete from specified tables" error

Create a unique index on the field [Key] in tblTempSkus. If you can't
do that, because tblTempSkus has duplicate keys, you can either (a)
remove the duplicates, or (b) try this query:

DELETE tblSkuList.*
FROM tblSkuList
WHERE tblSkuList.Key Not In
(SELECT tblTempSkus.Key FROM tblTempSkus);


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

The Index did it. Thanks Dirk.
Rosco