Operation must use an updateable query error (1 Viewer)

[Design by Sue]

I need to find duplicates in one table based on 2 fields, Name and UPC (if there are 2 records with the same Name and same UPC but the GCode is blank) which I have been able to create a select query using the Query Wizard and have made it DISTINCT.

I have then set up a query to find matching fields in another table. This needs to be a update query which changes a yes/no field to yes (the Exclude field) and adds text to a field with the Reason. The joins in this are inner joins but I have also tested a right join. I get the error message mentioned in the title. I did google this but am not understanding the answers.

Can someone please help me understand why I am getting this message and how can I update a second table based on the query of the first table?

Thanks
Sue

 

[Galaxiom]

UPDATE table2 
 SET Exclude = True
 WHERE table2.somefield IN(SELECT somefield FROM table1 WHERE yourcondition)

 

[Design by Sue]

Thanks - tried to do this as a subquery, which I think I understand you are suggesting, but I get the error "You have written a subquery that can return more than one field without using EXISTS reserved word in the main query's from clause. Revise the SELECT statement of the subquery to request only one field."

To create the code I used the code from the 2 separate queries and added the first one as the subquery.

Any thoughts on this?

 

[jdraw]

Sue,

Please post the exact SQL you used following Galaxiom's sample.

 

[Design by Sue]

Ended up using a recordset and an update statement.

Sue