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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
