UPDATE a ROW based on an UPDATE of a different ROW

classic Classic list List threaded Threaded
4 messages Options
jic
Reply | Threaded
Open this post in threaded view
|

UPDATE a ROW based on an UPDATE of a different ROW

jic

Greetings!

imagine this situation...

CREATE TABLE foo (id, PID, bdate, edate, lang,job);
INSERT INTO foo VALUES (1, 232,'2008-01-01','2008-01-10','es','trans');
INSERT INTO foo VALUES (2, 232,'2008-01-01','2008-01-10','fr','trans');
INSERT INTO foo VALUES (3, 232,'2008-01-01','2008-01-10','it','trans');
INSERT INTO foo VALUES (4, 232,'2008-01-01','2008-01-10','es','val');
INSERT INTO foo VALUES (5, 232,'2008-01-01','2008-01-10','fr','val');
INSERT INTO foo VALUES (6, 232,'2008-01-11','2008-01-11','it','val');
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';

sqlite> SELECT * FROM foo;
1|232|2008-01-01|2008-01-10|es|trans
2|232|2008-01-01|2008-01-10|fr|trans
3|232|2008-01-01|2008-01-10|it|trans
4|232|2008-01-10|2008-01-10|es|val
5|232|2008-01-10|2008-01-10|fr|val
6|232||2008-01-11|it|val
sqlite>

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.

thanks for any help you guys could provide,

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE a ROW based on an UPDATE of a different ROW

Igor Tandetnik
"jose isaias cabrera" <[hidden email]>
wrote in message
news:[hidden email]

> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE a ROW based on an UPDATE of a different ROW

jose isaias cabrera
In reply to this post by jic

"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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
jic
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE a ROW based on an UPDATE of a different ROW

jic
In reply to this post by jic

"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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users