Need help with this SQL

Question

Hi guys,

I am very very new to mysql. I am trying to execute the following query.

update share set holder = 22 where SHARE_ID IN (select SHARE_ID from SHARE WHERE holder=1 LIMIT 10)

When I try to execute the above query I am getting this error

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Answer 1

Your error is that you are using IN and MySQL doesn't support that, so you need another way to work your query. What you might want to consider doing, if this is not a query you need often, is dumping the "IN query" to a table and then doing a join and checking against that.

Answer 2

The error message is telling you the problem, the query syntax you are trying to use is not supported by the version of MySQL that you are using.

You will need to re-think your query to work with the processes supported by your current MySQL Version.

Answer 3

mysql 5.5 yet not support the limit in the sub query

http://dev.mysql.com/doc/refman/5.5/en/subquery-errors.html

u need to fix your query

Answer 4

UPDATE  share
SET     holder = 22
WHERE   holder = 1
ORDER BY
        share_id
LIMIT 10

Due to the optimizer issues, LIMIT is not supported in the IN clause.

Answer 5

why don't you try

UPDATE share 
SET holder = 22 
WHERE holder=1 LIMIT 10

For more information, however, you can the read mysql doc. "Limit" and "order by" can't be used with a multiple table update query, which is not your case here.