Unable to Delete After Unmatched Query (1 Viewer)

K

Ksabai

Registered User.

Local time

Yesterday, 22:01

Joined

Jul 31, 2017

Messages

104

• Sep 6, 2019

• #1

The Following is my Sql, iam able find the record to delete but iam unable to delete as its Says "Specify the table Containing the records you want to delete"

DELETE tblBciCMiA.ContNo, tblBciCMiA.InvNo, tblBciCMiA.Seller, tblBciCMiA.Buyer, tblBciCMiA.Qty, tblBciCMiA.Cert, tblBciCMiA.RQty, tblBciCMiA.RDate, qryBciCMiA.qInvNo
FROM tblBciCMiA LEFT JOIN qryBciCMiA ON tblBciCMiA.[InvNo] = qryBciCMiA.[qInvNo]
WHERE (((qryBciCMiA.qInvNo) Is Null));

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 13:01

Joined

May 7, 2009

Messages

19,233

• Sep 6, 2019

• #2

like it says, which table to delete?
obviously from tblBciCMiA.

DELETE tblBciCMiA.* FROM tblBciCMiA LEFT JOIN qryBciCMiA ON tblBciCMiA.[InvNo] = qryBciCMiA.[qInvNo]
WHERE (((qryBciCMiA.qInvNo) Is Null));

!!!BACKUP first your table!!!

 

June7

AWF VIP

Local time

Yesterday, 21:01

Joined

Mar 9, 2014

Messages

5,470

• Sep 6, 2019

• #3

There is no need to specify fields in DELETE because entire record is deleted.

If Arnel's suggestion doesn't work, try:

DELETE FROM tblBciCMiA WHERE ID IN (SELECT ID FROM tblBciCMiA LEFT JOIN qryBciCMiA ON tblBciCMiA.[InvNo] = qryBciCMiA.[qInvNo]
WHERE qryBciCMiA.qInvNo Is Null);

Deleting data should be a rare event. Why are you deleting?

 

K

Ksabai

Registered User.

Local time

Yesterday, 22:01

Joined

Jul 31, 2017

Messages

104

• Sep 6, 2019

• #4

it works, iam actually just trying how to delete a record.

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 13:01

Joined

May 7, 2009

Messages

19,233

• Sep 6, 2019

• #5

he, he, the best place to practice with thrill and chill is on live data!