Update specific table column with select query data MS Access

Question

I'm using MS Access 2019 and ahve two tables:

This UPDATE statement doesn't work:

UPDATE table_a
SET number_a = (SELECT COUNT(*) FROM table_b WHERE id_b IN (1,3) AND name_b=@name) 
WHERE id_a=1;

Error

Operation must use an updateable query

https://www.fmsinc.com/MicrosoftAccess/query/non-updateable/index.html — Tim Biegeleisen, Jan 15 '20 at 08:14
Does this answer your question? [Use SELECT inside an UPDATE query](https://stackoverflow.com/questions/871905/use-select-inside-an-update-query) — Volkan Albayrak, Jan 15 '20 at 08:18

Akina · Accepted Answer · 2020-01-15T09:02:09.583

1

UPDATE table_a 
SET table_a.number_a = DCount("*","table_b","id_b IN (1,3) AND name_b=@name")
WHERE table_a.id_a=1;

or

UPDATE table_a 
SET table_a.number_a = DCount("*","table_b","id_b IN (1,3) AND name_b=" & @name)
WHERE table_a.id_a=1;

depends on what is @name, where it is defined and how it is substituted with its value.

edited Jan 15 '20 at 09:02

answered Jan 15 '20 at 08:27

Akina

score 0 · Answer 2 · answered Jan 15 '20 at 08:16

0

Why don't you capture value then assign it like below

DECLARE @COUNT_B INT
SELECT @COUNT_B = COUNT(*) FROM table_b WHERE id_b IN (1,3) GROUP BY id_b

UPDATE table_a
SET number_a = @COUNT_B
WHERE id_a=1;

answered Jan 15 '20 at 08:16

2 Answers2