Iczer

Chain update of elements in SQLite table

2 posts in this topic

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;"

 

$sSqlBuild  = '(Item,id,param,Date_1,Date_2)'
;table :
;Item_01,123,aaa,Date_1,Date_2
;Item_01,124,aaa,Date_1,Date_2
;Item_01,125,bbb,Date_1,Date_2
;Item_01,126,bbb,Date_1,Date_2
;Item_01,127,bbb,Date_1,Date_2
;Item_01,128,aaa,Date_1,Date_2
;Item_01,129,aaa,Date_1,Date_2
;Item_01,130,bbb,Date_1,Date_2
;Item_01,131,aaa,Date_1,Date_2
;Item_01,132,aaa,Date_1,Date_2

 

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Let's look at the "previous row" thing.

Assume the table is like this:

Item    ID  Param   Date_1          Date_2
Item_01 123 aaa 2015-08-18 21:30:20 2015-08-18 22:01:48
Item_01 124 aaa 2015-07-16 21:30:30 2015-07-16 22:02:11
Item_01 125 bbb 2015-07-25 21:27:25 2015-07-25 22:09:37
Item_01 126 bbb 2015-09-14 21:27:34 2015-09-14 21:35:11
Item_01 127 bbb 2015-07-16 21:31:00 2015-07-16 22:25:56
Item_01 128 aaa 2015-06-18 21:31:32 2015-06-18 21:52:21
Item_01 129 aaa 2015-09-17 21:31:34 2015-09-17 22:10:18
Item_01 130 bbb 2015-08-14 21:31:36 2015-08-14 21:36:17
Item_01 131 aaa 2015-06-30 21:31:38 2015-06-30 22:22:33
Item_01 132 aaa 2015-07-07 21:31:50 2015-07-07 22:28:40

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):

update tbl set prevID = (select ID from tbl T where T.id < tbl.id order by T.id desc limit 1)

Now the dummy table has become (prevID was null before the UPDATE above):

Item    ID  Param   Date_1  Date_2  PrevID
Item_01 123 aaa 2015-08-18 21:30:20 2015-08-18 22:01:48 (null)
Item_01 124 aaa 2015-07-16 21:30:30 2015-07-16 22:02:11 123
Item_01 125 bbb 2015-07-25 21:27:25 2015-07-25 22:09:37 124
Item_01 126 bbb 2015-09-14 21:27:34 2015-09-14 21:35:11 125
Item_01 127 bbb 2015-07-16 21:31:00 2015-07-16 22:25:56 126
Item_01 128 aaa 2015-06-18 21:31:32 2015-06-18 21:52:21 127
Item_01 129 aaa 2015-09-17 21:31:34 2015-09-17 22:10:18 128
Item_01 130 bbb 2015-08-14 21:31:36 2015-08-14 21:36:17 129
Item_01 131 aaa 2015-06-30 21:31:38 2015-06-30 22:22:33 130
Item_01 132 aaa 2015-07-07 21:31:50 2015-07-07 22:28:40 131

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.

begin;
create temp table tt as
    select
        o.item,
        o.id,
        o.param,
        n.date_1,
        n.date_2
    from
        tbl o
            left outer join
        tbl n    
            on n."id" = (select ID from tbl t where t.id < o.id order by t.id desc limit 1)
;
update tbl set date_1 = (select date_1 from tt where tt.id = tbl.id), date_2 = (select date_2 from tt where tt.id = tbl.id) where param = 'bbb';
drop table tt;
commit;

 

I don't understand what you mean by "evenly distributed", so I don't have an answer for that part.

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

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)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now