Re: [sqlite] how to write this commands?

[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 (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