> Peter Bartholdsson wrote:
> > Is there some way to write this query in SQLite?
> > UPDATE
> > groups
> > SET
> > (fileCount, size) = (SELECT count(id), sum(size) FROM files where
> > groupId = 15)
> > WHERE
> > groupId = 15;
> >
>
> Sadly, no. SQLite only allows a subquery to return a single
> value, not a tuple.
I haven't seen too many others that would do that either, so no great loss.
However, will SQLite do this?
update groups
set filecount = result.count,
size = result.size
from (
select count(id), sum(size)
from files
where groupid = 15
) as result
where groupid = 15;
Alternative, if SQLite won't do that, select the "files" info into a
temporary table, and use the temporary table in the FROM clause above.
create temp table result as
select count(id), sum(size) from files where groupid = 15;
update groups
set filecount = result.count,
size = result.size
from result
where groups.groupid = 15
and result.groupid = 15;
It's not one statement, but it should be executable with one call, right?
