[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] UPDATE a ROW based on an UPDATE of a different ROW
From:       "jose isaias cabrera" <cabrera () wrc ! xerox ! com>
Date:       2008-12-23 4:54:49
Message-ID: 69226409E3CF4FEFADD2643BDB76E77C () stso ! mc ! xerox ! com
[Download RAW message or body]


"Igor Tandetnik" wrote...

>> 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';
>


thanks, Igor.

josé

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[prev in list] [next in list] [prev in thread] [next in thread]