2018-07-05 5:37 GMT+02:00 Simon Slavin <slav...@bigfraud.org>:
> On 5 Jul 2018, at 3:22am, Cecil Westerhof <cldwester...@gmail.com> wrote:
>
> > I only want to store a date with a record. But it is possible that more
> as
> > one record will be inserted, so I want to use another field to use as an
> > index. So that the first gets an one, the second a two, etc.
> > Is this possible, or do I just have to check if there is already a date
> and
> > fetch the highest index and increase this with one?
>
> There's no magical shortcut.
>
> I would create an index on (theDate, dateEventNumber). Then do
>
> BEGIN
> SELECT MAX(dateEventNumber) FROM MyTable WHERE theDate = ?1
> [ in your code see whether you got NULL back, substitute 0 ]
> INERT INTO MyTable ... ?1 + 1
> COMMIT
>
> You can combine the two commands into one more complicated thing, but I'd
> do that only if I was sure nobody would ever have to figure out why my code
> wasn't working.
>
I went for the following solution:
UPDATE selectRandom
SET lastUsed = DATE('now', 'localtime')
, lastUsedIdx = (
SELECT IFNULL(MAX(lastUsedIdx), 0)
FROM selectRandom
WHERE lastUsed = DATE('now', 'localtime')
) + 1
WHERE description = :teaToBrew
I would think that is not to convoluted.
--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
