Christoph Gassmann | aquantum GmbH wrote:
> I have a log-Table of a webpage-logger and for faster selects i need
> the seconds between to clicks.
>
> I found out a select-statement that produces the seconds. Here it is:
>
> SELECT round(julianday(t2.dTimestamp)*86400 -
> julianday(t1.dTimestamp)*86400) as secondsFromLast FROM tProject1Logs
> t1 INNER JOIN tProject1Logs t2 WHERE t1.sSession = t2.sSession AND
> t1.nfClicks = (t2.nfClicks - 1)
>
> Now I want to update my Table with this results. I have to write it
> in "t1.secondsFromLast". How can I do this?
Christoph,
The problem with an update is that any SELECT statement in the assignment
expression get executed before the update scan starts, so it can't depend on
the row being updated.
If this is a one time requirement, you can create a temp table with the
correct values using a modified version of your query which returns all the
columns of tProject1Logs. Then drop the original table and recreate it from
the temp table. You can probably accomplish the same thing by using INSERT
OR REPLACE to replace the records in the existing table with new records
using the same select statement.
If your table has a unique primary key then you could also create a second
table with a delta time column and use the primary key to relate it to the
original table (without any time difference column). You can use a view to
make the joined tables look like a single table.
Either way, you can use an insert trigger to set the time difference value
correctly in all new records at the time they are added. Something like this
should work.
CREATE TRIGGER in_tProject1Logs AFTER INSERT ON tProject1Logs
BEGIN
UPDATE tProject1Logs
SET secondsFromLast =
(SELECT round(julianday(new.dTimestamp)*86400 -
julianday(t1.dTimestamp)*86400)
FROM tProject1Logs AS t1
WHERE t1.sSession = new.sSession AND t1.nfClicks =
(new.nfClicks - 1))
WHERE primayKey = new.primaryKey;
END;
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
