Update query in Sqlite

Question

I was trying to update only 10 records sorted by date in sqlite.

I am using this query but not able to proceed further getting exception at limit.

UPDATE TABLENAME  SET COLUMMANGE = 'VALUE' WHERE  1  LIMIT 10 ORDER BY COLUMNNAME ASC

WHERE is condition so you should write something like `WHERE ID = 1`, simply `WHERE 1` is invalid syntax. — Lucifer, Apr 09 '14 at 08:03

score 3 · Accepted Answer · answered Apr 09 '14 at 08:05

I am assuming that you have one field named ID in your table, based on it your query to updated 10 latest record should be done following way,

UPDATE TABLENAME  SET COLUMMANGE = 'VALUE' WHERE 
ID in ( SELECT ID from TABLENAME ORDER BY COLUMNNAME DESC LIMIT 10 );

Here inner query will select 10 latest record id from the table. These Ids will be passed to outer Update query.

score 1 · Answer 2 · edited May 23 '17 at 10:26

I believe you need an nested query first to fetch the primary keys (or some other key) of the rows you want to update, and than update only those rows. Try something like this:

 UPDATE TABLENAME SET COLUMMANGE='VALUE'
 WHERE id IN (
     SELECT id FROM (
         SELECT id FROM TABLENAME
         WHERE <YOUR CONDITION>
         ORDER BY COLUMNNAME ASC
         LIMIT 10
     )
 );

It is also been discussed here if you want more details: update multiple rows using limit in mysql?.

But really think if you really want this behaviour. Also the WHERE 1 is not a part of the valid SQL syntax .

`WHERE 1` is valid (but superfluous). – CL. Apr 09 '14 at 08:21 — CL., Apr 09 '14 at 08:21

score 1 · Answer 3 · answered Apr 24 '14 at 10:49

Try this query

UPDATE TABLE_NAME  SET COLUMN_NAME = 'VALUE' WHERE PRIMARYKEY_COLUMN in ( SELECT PRIMARYKEY_COLUMN  from TABLE_NAME ORDER BY COLUMNNAME DESC LIMIT 10 );

PRIMARY KEY COLUMN IS MANDATORY IN THIS TABLE.
THE ABOVE QUERY RESULTS TOP 10 RECORDS IN DESCENDING ORDER

Update query in Sqlite

3 Answers3

Linked