Igor Tandetnik wrote:
> Baruch Burstein <bmburst...@gmail.com> wrote:
>> Does sqlite not support table aliases in update statements?
>
> Indeed it does not.
>
>> Is there a way
>> to work around this to get the affect of
>>
>> update table1 t1
>> set col1 = col1 * 2
>> where col1 <= (select avg(col1)
>> from table1
>> where col2=t1.col2);
>
> update table1 set col1 = col1 * 2
> where col1 <= (
> select avg(col1) from table1 t2 where table1.col2=t2.col2);
>
> "table1" prefix binds to the first occurrence of this table in the statement,
> in the outer UPDATE clause.
Note: this request is quite inefficient; it is much better rewritten as
UPDATE table1 SET col1 = col1*2
WHERE rowid in (SELECT t1.rowid
FROM table1 t1
JOIN (SELECT avg(col1) AS avgcol1, col2
FROM table1 GROUP BY col2) t2
USING (col2)
WHERE t1.col1 < t2.avgcol1);
(assuming you have indexes on col1 and col2);
On table of ~500 000 random records, generated with
for(0..1_000) { $col2 = rand; for(0..int(rand(1_000))) {
$sth->execute(rand,$col2);
}}
Your query:
CPU Time: user 620.410773 sys 2.216138
0|0|0|SCAN TABLE table1 (~243273 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 0
0|0|0|SEARCH TABLE table1 AS t2 USING INDEX c2idx (col2=?) (~487 rows)
My query:
CPU Time: user 6.992437 sys 2.056128
0|0|0|SEARCH TABLE table1 USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)
0|0|0|EXECUTE LIST SUBQUERY 0
1|0|0|SCAN TABLE table1 USING INDEX c2idx (~486546 rows)
0|0|1|SCAN SUBQUERY 1 AS t2 (~100 rows)
0|1|0|SEARCH TABLE table1 AS t1 USING INDEX c2idx (col2=?) (~162 rows)
Speedup by whooping 8772%!
Of course, this is very content-dependent (in my test table
COUNT(*)/COUNT(DISTINCT col2) == 500; if that ratio lower, effect of conversion
is lower; if there are *very* many distinct col2, my query may be even slower).
And, of course, if you need use subselect result in SET expression, such query
conversion impossible (that's why lack UPDATE ... JOIN is very important :-|
[lack of DELETE ... JOIN is much less important - *any* DELETE ... JOIN can be
converted to DELETE FROM WHERE rowid in (SELECT rowid FROM ... JOIN ...]);
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
