Delete query error message:"specify the table containing records u want to delete"

Question

I want to delete all records from table "T" if the records are also in table D, but I am getting the error message "SPECIFY THE TABLE CONTAINING THE RECORDS YOU WANT TO DELETE".

DELETE T.DISCOUNT_CODE, T.PART_ID, T.SELLING_UM, T.QTY_BREAK_1, T.QTY_BREAK_2, T.QTY_BREAK_3, T.QTY_BREAK_4, T.QTY_BREAK_5, T.QTY_BREAK_6, T.QTY_BREAK_7, T.QTY_BREAK_8, T.QTY_BREAK_9, T.QTY_BREAK_10, T.UNIT_PRICE_10, T.UNIT_PRICE_3, T.UNIT_PRICE_4, T.UNIT_PRICE_5, T.UNIT_PRICE_6, T.UNIT_PRICE_7, T.UNIT_PRICE_8, T.UNIT_PRICE_9, T.UNIT_PRICE_2, T.UNIT_PRICE_1, T.DEFAULT_UNIT_PRICE
 FROM SYSADM_DISCOUNT_PRICE AS T 
 INNER JOIN D ON T.PART_ID = D.PART_ID;

Answer 1

Create a new query in Access, switch to SQL View and paste this in:

DELETE 
FROM SYSADM_DISCOUNT_PRICE AS T
WHERE Exists (SELECT * FROM D WHERE D.PART_ID = T.PART_ID);

That should work if you "run" (execute) the query. If you want to View the affected rows without actually doing the DELETE, you can switch the query designer view to Datasheet View. That will show you which rows will be subject to deletion, but Datasheet View only displays them ... doesn't delete them. However, in order to switch to datasheet view, you will have to tell the query designer which fields to display. You can do that by changing the first line to DELETE *.

Another approach would be to change the first line to SELECT * in order to view the affected records, then change back to DELETE when you're ready to delete them.

Answer 2

MS Access does let you join on a delete. So you can write you DELETE statement like this without naming the columns.

DELETE T.*
FROM SYSADM_DISCOUNT_PRICE AS T 
INNER JOIN D ON T.PART_ID = D.PART_ID;

Answer 3

You don't specify columns in a delete statement. You also can't join on delete statements.

Basically, you delete from table . So, put all your conditional logic in a where clause.

Where recordid in (select .........)