I think Simon's solution is in error, and Igor's is correct. In Simon's
case, Slevel will be set to 1 if price1 is greater than 30. However, the
original c function would set Slevel to 2 because price1 is greater than 12
and it is greater than 30. Two increments of i are executed in that
scenario. Igor's accumulating the boolean results gets you where you want
to be; it's just a little cryptic.
The original function was less than explicit. I'm assuming some kind of
weighted value is being generated. My solution is probably the most wordy
and least efficient performance wise, it is explicit in function. It also
allows you to set the weight of each price1/price2 level.
UPDATE bb SET Slevel =
(
(Case when price1>12 then 1
when price1>20 then 2
else 0
End) +
(Case when price2>20 then 1
when price2>30 then 2
when price2>80 then 3
else 0
End)
);
On Thu, Mar 1, 2012 at 7:21 AM, Igor Tandetnik <itandet...@mvps.org> wrote:
> YAN HONG YE <yanhong...@mpsa.com> wrote:
> > I have a sqlite database named bb:
> >
> >> Name Price1 Price2 Slevel
> >> A1 23 231 NULL
> >> A2 22 12 NULL
> >> A3 21 223 NULL
> >
> > My question is:
> > I want to update culumn Slevel by function myfunc():
> >
> > int myfunc():
> > {int i=0;
> > if (price1 >12)
> > i++;
> > if (price1>30)
> > i++;
> > if (price2>20)
> > i++;
> > if (price2>30)
> > i++;
> > if (price2>80)
> > i++;
> > return i;
> > }
>
> Just run this query:
>
> update bb set Slevel = (price1>12) + (price1>30) + (price2>20) +
> (price2>30) + (price2>80);
>
> --
> Igor Tandetnik
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
