"jose isaias cabrera" <cabr...@wrc.xerox.com>
wrote in message
news:5c0942b2619c4403b47ddf72586c8...@stso.mc.xerox.com
> As you can see, this UPDATE,
>
> UPDATE foo SET bdate = (SELECT f2.edate FROM foo f1 JOIN foo
> f2 ON f1.PID = f2.PID AND f1.lang = f2.lang WHERE f1.job = 'val' AND
> f2.job = 'trans' AND f1.id = foo.id AND f1.bdate < f2.edate)
> WHERE foo.job = 'val';
>
> clears the bdate of line 6, but 4 and 5 worked correctly. I did some
> searches on the internet to try to find out how to get it to work,
> but could not figure it out. I thought of a CASE, but couldn't
> figure it out. Newbie, of course. What I would like to do is to
> update the bdates of the 'val' jobs with the edate of the 'trans' job
> of the corresponding lang, only if the bdate of the 'val' job is <
> the edate of the 'trans' job. I hope I am clear enough to get some
> help.
update foo set bdate = coalesce(
(select f1.edate from foo f1
where f1.job='trans' and f1.PID=foo.PID and
f1.lang=foo.lang and foo.bdate < f2.edate),
bdate)
where job = 'val';
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
