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

Does this answer your question? Use SELECT inside an UPDATE query — Volkan Albayrak
– Volkan Albayrak, Commented Jan 15, 2020 at 8:18

Akina · Accepted Answer · 2020-01-15 09:02:09Z

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.

answered Jan 15, 2020 at 8:27

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nguyễn Văn Phong · Accepted Answer · 2020-01-15 08:16:27Z

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, 2020 at 8:16

14.2k19 gold badges48 silver badges65 bronze badges

This is not valid syntax for MS Access.