MariaDB, UPDATE with SELECT, LIMIT in subquery

January 5, 2024

I am trying to update a field in a table using a SELECT query, when I execute I see the errors:

08:10:16    UPDATE TABLE `Tesla-20240105` SET `state`=1 WHERE `id` IN (SELECT `id` FROM `Tesla-20240105` WHERE `state` IS NULL LIMIT 10)    Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TABLE `Tesla-20240105` SET `state`=1 WHERE `id` IN (SELECT `id` FROM `Tesla-2...' at line 1  0.00013 sec
08:10:48    UPDATE `Tesla-20240105` SET `state`=1 WHERE `id` IN (SELECT `id` FROM `Tesla-20240105` WHERE `state` IS NULL LIMIT 10)  Error Code: 1235. This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'    0.040 sec

The table can have potentially 300,000 records, the above is an attempt to select just 10 where the state is NULL and modify them, any suggestions on how I can achieve this ?

>Solution :

How about moving limit out of subquery, into the main query?

UPDATE `Tesla-20240105` SET 
  `state`=1 
WHERE `id` IN (SELECT `id` 
               FROM `Tesla-20240105` 
               WHERE `state` IS NULL
              ) 
LIMIT 10