Chain update of elements in SQLite table

2 posts in this topic

Iczer 13

I need update SQLite table, so "Date_1" and "Date_2" elements in rows with param = 'bbb' become equal "Date_1" and "Date_2" in previous row in order by "id" or, better, their values will be evenly distributed between rows with param = 'aaa'.

But i don't know, how to get value from previous row in Sql Query...

Date_1 and Date_2 is Combined date and time in ISO 8601: "2015-10-08T15:04:46+00:00"

$sSql_Query="UPDATE TestDB SET Date_1 = {value from previous row}, Date_2 = {value from previous row} WHERE param IS 'bbb' ORDER BY id DESC;"

Do you want that row with ID 125 gets dates from row with ID 124; and row 126 those of previous 125 (before update), and so on?

If so that isn't possible in SQL because for that to work you would need to process rows from the highest ID and proceed with ID decreasing. Unfortunately (or not) SQL tables don't have an internal order and the ORDER BY clause is only valid for SELECT statements.

Ignoring this it's possible to get values from a preceeding row (more precisely the unique row with ID immediately smaller) by using a correlated subquery. Given that you wanted to update two columns, this subquery would have been needed twice.

Let's add a new column to the table and call it prevID. Here's how you can update the table by updating prevID and storing there the ID of the previous row (ID-wise):

All in all the short answer to the first question ("previous row") is that you need to do that using non-SQL code because you have to proceed backwards (ID-wise).

EDIT: it's still possible to "shift" dates from previous row by creating two extra columns, store there dates from previous row, move them to the regular date columns and remove the extra columns. But you'd have better time coding that in AutoIt IMHO. And since it involves manipulating the schema to perform the task, I wouldn't call that actual pure SQL (and SQLite doesn't support DROP COLUMN even if you can achieve this using 3rd-party tools like SQLite Expert). For this trick to work, there must be no unique index on target columns else the result may be undefined or cause errors.

EDIT2: in fact you can do that in plain SQL even if I don't recommend doing so unless you know exactly what you're doing.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)