The correlated query was not accepted in the select statement. However, I
modified your original UPDATE query, as follows, and it did execute, and
passed the proper quantity of rows for the first zip/crrt combination,
only. The rows were not updated. It counted correctly, but did not change
the values.
sql> UPDATE tmp_addresses
set segment='xx'
where rowid in (
select x2.rowid from tmp_addresses x2 where x2.zip=zip and x2.crrt=crrt
order by
CASE
WHEN x2.version_id = '0060' THEN 0
WHEN x2.version_id = '0064' THEN 2
WHEN x2.version_id = '0061' THEN 3
ELSE 99
END ASC
limit ifnull( (
select net_non_pieces from crrt_net_non net
where net.zip=zip and net.crrt=crrt
), 0)
)
[2015-10-15 06:54:18] *42 row(s)* affected in 189ms
I have not yet attempted your most recent suggestion. I will get to that
some time today.
On Wed, Oct 14, 2015 at 9:21 PM, Igor Tandetnik <igor at tandetnik.org> wrote:
> On 10/14/2015 9:29 PM, Don V Nielsen wrote:
>
>> But I am having problems with the LIMIT statement. It throws an exception
>> no matter what table alias is used: X or x2. It says "no such column".
>>
>
> Ah, interesting. LIMIT clause doesn't appear to allow correlated
> subqueries; only self-contained expressions. Here goes that idea.
>
> Something like this should work:
>
> select X.*
> from addresses X join crrt_net_non_pieces using (zip, crrt)
> where net_non_pieces > (
> select count(*) from addresses x2
> where X.zip=x2.zip and X.crrt=x2.crrt and
> (x2.version_id < X.version_id or (x2.version_id = X.version_id and
> x2.rowid < X.rowid))
> );
>
> I took the liberty to simplify the ordering expression, for purposes of
> exposition (it's very long and would need to be repeated four times).
> Replace all occurrences of T.version_id with your CASE clause, calculated
> against table T. Also, I'm breaking ties by rowid; your original problem
> statement is underspecified unless there's a total order on Addresses.
>
> --
> Igor Tandetnik
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
