[Resolved] Increment sqlite field

timcapulet

I have a sqlite table called 'table' with 2 columns, 'index' and 'number'. I want to add 1 to 'number' in a particular row.

Is there an easy way to do this with a single SQL query? It would be nice to not have to do a query, set it to a variable, add 1, and then do another query. I envision it will look something like this:

$query = "UPDATE table SET number + 1 WHERE index == '2'";

Thanks,
Tim

Installer

Probably more like

$query = "UPDATE table SET number = number + 1 WHERE index == '2'";

timcapulet

It worked. Thank you very much.

-Tim

timcapulet

Now I have another question. Is there a way that I can limit the value ? For example, if 'number' equals 6 and I used

$query = "UPDATE table SET number = number + 5 WHERE index == '2'";

Is there a way that I could make sure that number gets no greater than 10?

laserlight

Most probably something like:
UPDATE table SET number=(number+5)%10 WHERE index='2';

Actually, to be on the safe side, I would treat 'table' and 'index' as reserved words, since that is what they should be. "index=='2'" should be "index='2'" to the best of my knowledge.

$query = "UPDATE [table] SET number=(number+5)%10 WHERE [index]='2'";

Installer

"INDEX" and "TABLE" are indeed SQLite keywords. Either "=" or "==" can be used for comparison (reference).

timcapulet

This code:

$query = "UPDATE [table] SET number=(number+5)%10 WHERE [index]='2'";

Will set the value to the remainder of (number+5)/10, in this case 1. I actually solved the problem with a mix of php and sqlite.

Function check_input($a)
{
	if ( $a > 10 ){ return 10; }
	else{ return $a; }
}
sqlite_create_function($db, 'CHECK_INPUT', 'check_input', 1);
$query = "UPDATE picture SET score = CHECK_SCORE(score + $amount) WHERE picture_id == $pic_id";
sqlite_query($db, $query);

I had never used sqlite_create_function function before, and it works very nicely. Thanks for everyone's input.

-Tim