Software

Ask something
Question Locked

MS Access SQL - DELETE

0 helpful
9 replies

by martinbullock15 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi

I am trying to Delete from one table depending where records match between to tables. I'm using Access 97 due to the type of database

So i have a table with a list of codes and a second table with the list of codes which i want to delete, this is my statement:

DELETE tblProduct.*
FROM tblProduct
WHERE tblProduct.ProductBarcode = tblProduct_SU.ProductBarcode;

From this i get a Parameters box asking my to enter something.

Any help would be very grateful

Thanks in advance

Martin

9 replies

by martinbullock15

Hi

I understand what you mean as i tried adding tblProduct_SU to the FROM and then i get the following message when i try to run it:

"Operation must use an updatable query"

Thanks again

by shellbot

Its asking you for the Barcode, as you have said "WHERE tblProduct.ProductBarcode = tblProduct_SU.ProductBarcode;"

brain is holiday right now..grrr..

use the designer if ye can, add both tables, link them on ProductBarcode, change to delete query, and use the
tblProduct.ProductBarcode = tblProduct_SU.ProductBarcode
criteria in the conditions..

should work?

by martinbullock15

Hi

Thanks for your suggestion,

I have tried to you the query builder (which is horrible anyways) but it won't allow me to use the tblProduct.* as i am unable to select WHERE as it only allows FROM.

Thanks

Martin

by shellbot

ya, designer is horrible..

DELETE Table1.*
FROM Table1 INNER JOIN table2 ON Table1.id = table2.id
where table1.id=table2.id;

this works

table1= tblProduct.ProductBarcode
table2=tblProduct_SU.ProductBarcode
id=ProductBarcode

by martinbullock15

Thanks for your help

I tried your solution and i got the same message when trying to run the query

"Operation must use an updatable query"

by shellbot

where you running the query?
from designer??

if not, open er up, and go to sql view, paste it in, and give it a go..

by tony hopkinson

Delete from TBLProduct Where ProductBarCode in (select ProductBarCode From tbl_ProductSU)

This discussion is closed to new replies.