Question

Locked

MS Access SQL - DELETE

By martinbullock15 ·
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

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Where...

by cmiller5400 In reply to MS Access SQL - DELETE

Where is "tblProduct_SU.ProductBarcode" defined?

Collapse -

where: reply

by martinbullock15 In reply to Where...

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

Collapse -

Its doing what it should

by Shellbot In reply to MS Access SQL - DELETE

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?

Collapse -

RE: Its doing what it should

by martinbullock15 In reply to Its doing what it should

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

Collapse -

This works

by Shellbot In reply to RE: Its doing what it sho ...

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

Collapse -

RE: This works

by martinbullock15 In reply to This works

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"

Collapse -

odd

by Shellbot In reply to RE: This works

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..

Collapse -

Try

by Tony Hopkinson In reply to MS Access SQL - DELETE

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

Back to Software Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums