Re: [sqlite] how to write this commands?

2014-05-19 Thread 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

Re: [sqlite] how to write this commands?

2014-05-19 Thread James K. Lowden
On Mon, 19 May 2014 09:55:25 +0300 Paul de...@ukr.net 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

Re: [sqlite] how to write this commands?

2014-05-16 Thread 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

Re: [sqlite] how to write this commands?

2014-05-16 Thread 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

Re: [sqlite] how to write this commands?

2014-05-16 Thread Petite Abeille
On May 16, 2014, at 6:25 PM, Igor Tandetnik i...@tandetnik.org 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

Re: [sqlite] how to write this commands?

2014-05-16 Thread 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,

Re: [sqlite] how to write this commands?

2014-05-16 Thread 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?

2014-05-16 Thread James K. Lowden
On Thu, 15 May 2014 18:02:43 +0300 Paul de...@ukr.net 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

[sqlite] how to write this commands?

2014-05-15 Thread YAN HONG YE
update adla1 set PFLOPF=(SELECT pflopf from adl where adla1.ref=adl.ref) where select count(adl.ref) from adl=1; ___ 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?

2014-05-15 Thread 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

Re: [sqlite] how to write this commands?

2014-05-15 Thread 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