UPDATE multiple row in SQLITE

Question

I'm using SQLite and I'm trying to update multiple row using a select statement.

I'm having two tables:

device :

ID|Name|param1|param2
----------------------
1 | D1 |  p1  |  p2
2 | D2 |  p1  |  p3
3 | D3 |  p1  |  p4

devices_in_groups

ID|ID_group|ID_device|
----------------------
1 |    0   |    1    |
2 |    0   |    2    |
3 |    1   |    3    |

I would like to update all param1 attribut from all devices in the group 0.

I try something like:

UPDATE device SET param1 = p10 
WHERE device.ID = (
SELECT ID_device 
FROM devices_in_groups 
WHERE devices_in_groups.ID_group = 0)

It only update the first device (D1) and not the second (D2) unlike there both in the group 0.

How can i do it (in SQLite) ?

Answer 1

Use the operator IN:

UPDATE device 
SET param1 = 'p10' 
WHERE ID IN (
  SELECT ID_device 
  FROM devices_in_groups 
  WHERE ID_group = 0
);

See the demo.

If your version of SQLite is 3.33.0+ you could also do it with UPDATE...FROM:

UPDATE device AS d 
SET param1 = 'p10'
FROM devices_in_groups AS g 
WHERE d.ID = g.ID_device AND g.ID_group = 0;

See the demo.