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) ?

forpas · Accepted Answer · 2021-05-04T17:08:24.190

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.

edited May 04 '21 at 17:08

answered May 04 '21 at 16:49

forpas

score 1 · Answer 2 · answered May 04 '21 at 16:59

Even though your sub query

SELECT ID_device 
FROM devices_in_groups 
WHERE devices_in_groups.ID_group = 0

returns

ID_device
1
2

The main query needs to use IN rather than =

= implies you are comparing device.ID value to a query result (rows)

Also p10 needs to be expressed as a string as otherwise you will get an error as there is no column p10.

So changing the query to

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

Will work and update all the records in devices table as you expected.

2 Answers2