Re: [sqlite] how to write this commands?

[James K. Lowden]

On Mon, 19 May 2014 09:55:25 +0300
Paul  wrote:

> > UPDATE adla1 
> > SET pflopf = (
> > SELECT pflopf 
> > FROM adl 
> > WHERE adl.ref = adla1.ref) 
> > WHERE (
> > SELECT COUNT(*) 
> > FROM (
> > SELECT 1 FROM adl 
> > WHERE adl.ref = adla1.ref 
> > LIMIT 2
> > )
> > ) = 1;
> > 
> > Not all sure what LIMIT 2 does there. I think a SQL-92 version
> > would be
> > 
> 
> Limit, limits number of rows selected by ref. If we have found 2, no
> need to lookup further. That's why it was said to be a slightly
> optimized version. Generally this saves some disk reads.

I'll take your word for it, because it's redundant.  Although the WHERE
clause is rather awkwardly expressed, it does contain sufficient
information that the query planner could skip any "adl" as soon as it
sees that COUNT(*) will exceed 1.  

My rewrite used HAVING.  I would hope that SQLite wouldn't insist
on computing all aggregations for a WHERE EXISTS ... HAVING correlated
subquery.  That would be, er, suboptimal.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to write this commands?

[Paul]

> UPDATE adla1 
> SET pflopf = (
> SELECT pflopf 
> FROM adl 
> WHERE adl.ref = adla1.ref) 
> WHERE (
> SELECT COUNT(*) 
> FROM (
> SELECT 1 FROM adl 
> WHERE adl.ref = adla1.ref 
> LIMIT 2
> )
> ) = 1;
> 
> Not all sure what LIMIT 2 does there. I think a SQL-92 version
> would be
> 

Limit, limits number of rows selected by ref. If we have found 2, no need to 
lookup further.
That's why it was said to be a slightly optimized version. Generally this saves 
some disk reads.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to write this commands?

[James K. Lowden]

On Thu, 15 May 2014 18:02:43 +0300
Paul  wrote:

> > update adla1 set PFLOPF=(SELECT pflopf from adl where
> > adla1.ref=adl.ref) where select count(adl.ref) from adl=1;
> 
> A bit optimized version...
> 
> UPDATE adla1 
> SET pflopf = (SELECT pflopf FROM adl WHERE adl.ref = adla1.ref) 
> WHERE (SELECT COUNT(*) FROM (SELECT 1 FROM adl WHERE adl.ref =
> adla1.ref LIMIT 2)) = 1;

UPDATE adla1 
SET pflopf = (
SELECT pflopf 
FROM adl 
WHERE adl.ref = adla1.ref) 
WHERE (
  SELECT COUNT(*) 
  FROM (
   SELECT 1 FROM adl 
   WHERE adl.ref = adla1.ref 
   LIMIT 2
)
) = 1;

Not all sure what LIMIT 2 does there.  I think a SQL-92 version
would be

UPDATE adla1 
SET pflopf = (
SELECT max(pflopf) 
FROM adl 
WHERE ref = adla1.ref 
group by ref
)
WHERE exists (
select 1
FROM adl 
WHERE ref = adla1.ref 
group by ref
having count(*) = 1
);

It would be nice to use a CTE for the subqueries, but afaik that's not
possible.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to write this commands?

[mm.w]

>> Note to self: Someday, you want to be like Igor.

in which way LOL.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to write this commands?

[jose isaias cabrera]

"Igor Tandetnik" wrote...


On 5/16/2014 11:02 AM, Rob Richardson wrote:
It took me a bit of looking, but I think I understand your query.  One 
question remains:  why did you use the max() function?


It is, technically, not legal in SQL to use both an aggregate function

... [clip]
matter which, they all work the same when there's only one row to 
aggregate.

--
Igor Tandetnik


Note to self: Someday, you want to be like Igor.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to write this commands?

[Petite Abeille]

On May 16, 2014, at 6:25 PM, Igor Tandetnik  wrote:

> So with SQLite, the query without max() would work, and produce expected 
> results. With another database engine that enforces SQL rules more strictly, 
> the query without max() would fail with a syntax error. I figured I'd do it 
> by the book and wrap the field into an aggregate function. I could have used 
> max(), or min(), or avg() - it doesn't matter which, they all work the same 
> when there's only one row to aggregate.

Good man.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to write this commands?

[Igor Tandetnik]

On 5/16/2014 11:02 AM, Rob Richardson wrote:

It took me a bit of looking, but I think I understand your query.  One question 
remains:  why did you use the max() function?


It is, technically, not legal in SQL to use both an aggregate function 
and a direct field access on the same table. Use of an aggregate 
function means that an aggregation is performed, that multiple rows of 
the input table are grouped together in some way to produce one row of 
the result. If you also access a filed directly, without specifying how 
it should be aggregated, then the engine wouldn't know which of the many 
rows in the group the value in the output should be coming from.


Now, in this particular case the issue is moot, for two reasons. First, 
we are explicitly checking for count(*)=1, thus only dealing with groups 
that contain exactly one row, and so the ambiguity fails to arise. 
However, while it's obvious to a human reading the statement, it 
wouldn't generally be obvious to the SQL engine parsing it - requiring 
it to understand the semantics of the query to such an extent is really 
asking too much.


Second, SQLite allows this mixing of aggregate functions and raw fields, 
as a non-standard extension. When you do that, it just picks the value 
from an arbitrary row in the group (I'm simplifying a bit here) - which 
is not a problem in our case, since we know that the group consists on 
one row anyway.


So with SQLite, the query without max() would work, and produce expected 
results. With another database engine that enforces SQL rules more 
strictly, the query without max() would fail with a syntax error. I 
figured I'd do it by the book and wrap the field into an aggregate 
function. I could have used max(), or min(), or avg() - it doesn't 
matter which, they all work the same when there's only one row to aggregate.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to write this commands?

[Rob Richardson]

Igor,

It took me a bit of looking, but I think I understand your query.  One question 
remains:  why did you use the max() function?

Thanks!

RobR

-Original Message-

update adla1 set PFLOPF=(
   select case count(*)=1 then max(adl.pflopf) else adla1.pflopf end
   from adl where adla1.ref=adl.ref);

This says: for each row in adla1, if adl has exactly one row with the same ref 
value, then set adla1.pflopf to adl.pflopf taken from that one matching row. 
Otherwise, leave adla1 row unchanged.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to write this commands?

[Paul]

> update adla1 set PFLOPF=(SELECT pflopf from adl where adla1.ref=adl.ref) 
> where select count(adl.ref) from adl=1;

A bit optimized version...

UPDATE adla1 
SET pflopf = (SELECT pflopf FROM adl WHERE adl.ref = adla1.ref) 
WHERE (SELECT COUNT(*) FROM (SELECT 1 FROM adl WHERE adl.ref = adla1.ref LIMIT 
2)) = 1;

Don't know your structure of database, but I advice you to use 'FOREIGN KEY's.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to write this commands?

[Igor Tandetnik]

On 5/15/2014 6:03 AM, YAN HONG YE wrote:

  update adla1 set PFLOPF=(SELECT pflopf from adl where adla1.ref=adl.ref) 
where select count(adl.ref) from adl=1;


Are you looking for something like this?

update adla1 set PFLOPF=(
  select case count(*)=1 then max(adl.pflopf) else adla1.pflopf end
  from adl where adla1.ref=adl.ref);

This says: for each row in adla1, if adl has exactly one row with the 
same ref value, then set adla1.pflopf to adl.pflopf taken from that one 
matching row. Otherwise, leave adla1 row unchanged.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users