Counter variable in SQLite

I'm working on a simple todo app that has a column for each day of the week. On startup, the app checks to see if there are any incomplete tasks from before the current date. If so, they're moved to the top of the current date's column.

The app is cloud-based, but the tasks a开发者_StackOverflowre backed up for offline mode with an SQLite db. I can easily move the tasks by updating their date property, but I need the order property of each task to increment starting at 0 to place them at the top.

I need to be able to define a count variable in SQLite alone and increment it with each update that's performed. I know this code doesn't work, but it's an easy way of explaining what needs to be done:

count = 0

UPDATE `tasks` 
   SET `date` = '2010-01-04', 
       `order` = `count`++ 
 WHERE `date` < '2010-01-04'

I'd rather not use a temporary table or use a counter outside of SQLite, if possible.

@OMG Ponies - I'm starting to think it's just not possible all in SQLite.

I would do it with a second SQL-Statement:

UPDATE `tasks`
SET `date` = '2010-01-04',
count = ( SELECT COUNT(`task_id`) FROM `tasks` ) + 1
WHERE `date` < '2010-01-04'

Not tested but should work!

SQLite is a great tool, but I'd be surprised if this was possible using JUST SQLite. T-SQL support isn't that great within that app.

You could probably update the existing value in the record; adding or subtracting from it, but that would require the records to already be in order.

Actually, you can use addition in Sqlite - check out http://www.sqlite.org/lang_expr.html ; you just need to use an extra UPDATE statement to increment the counter and an inner query to get it again. Make sure to do this all in a Transaction or else you'll get concurrency problems.

it sounds to me that you'd like a a count column for each row. If i were you, i think you could do the call as

UPDATE `tasks` 
SET 
`date` = '2010-01-04', 
count = count + 1
WHERE `date` < '2010-01-04'

lemme know how that works...