On Thu, 15 May 2014 18:02:43 +0300
Paul <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
