MYSQL/MariaDB - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subqu-CodePudding

I try to transfer my python script for the database from SQLite to MariaDB.

This is the code that i use.

UPDATE users SET x = (%s) WHERE id IN (select id FROM users WHERE user_id = (%s) ORDER BY id DESC LIMIT 1)

I got this error mysql.connector.errors.ProgrammingError: 1235 (42000): This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

How can i fix this error?

CodePudding user response：

Use multiple-table UPDATE syntax.

UPDATE users 
NATURAL JOIN ( SELECT id 
               FROM users 
               WHERE user_id = (%s) 
               ORDER BY id DESC LIMIT 1 ) criteria
SET x = (%s);