zaher dirkey wrote:
> REPLACE INTO States
> (Material, Name, Qnt)
> values (1, 'MyName', Qnt + 1)
>
> I want to increase value of Qnt if the record already exist and insert it
> with 1 value if not exist, the default value for Qnt is 1 and it is NOT NULL
>
The default value is only used if you don't provide a value for that
column when you insert. It is not an initial value for a column.
To do what you want you need to query the database to get the current
value of Qnt before you do the insert.
insert or replace into States(Material, Name, Qnt)
values (1, 'MyName',
coalesce((select Qnt from States
where Material = 1 and Name = 'MyName') + 1,
1)
);
The subselect will return the current value, or null if the record
doesn't exist. Null + 1 is still null, so the coalesce() function wil
use the second value, 1, if there is no existing record. If the record
exists, its Qnt value will be incremented and inserted as a replacement
record.
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
