On 12/20/08, jose isaias cabrera <cabr...@wrc.xerox.com> wrote: > > Greetings! > > Imagine these rows in a table named LSOpenJobs: > > id, PID,subject, bdate, edate, lang,job > 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-01,2008-01-10,es,val > 5, 232,2008-01-01,2008-01-10,fr,val > 6, 232,2008-01-01,2008-01-10,it,val > > What I would like to do is to create a trigger to update the bdate for the > 'val' job of the same lang and same PID, with the edate of the 'trans' job > of same lang and same PID. For example, in this case above, let's take id > 1; the bdate for the 'val' job with the 'es' lang with the same PID, id 4, > should be updated with the edate of id 1. So, the trigger should UPDATE the > table to this, > > id, PID,subject, bdate, edate, lang,job > 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-10,2008-01-10,it,val > > The trigger example in the site expects to change the same row. This UDPATE > is based on other rows of the same table and same PID. > > Any help is greatly appreciated. >
José, Consider the following [04:43 PM] ~$sqlite3 SQLite version 3.5.9 Enter ".help" for instructions sqlite> CREATE TABLE foo (id, PID, bdate, edate, lang,job); sqlite> INSERT INTO foo VALUES (1, 232,'2008-01-01','2008-01-10','es','trans'); sqlite> INSERT INTO foo VALUES (2, 232,'2008-01-01','2008-01-10','fr','trans'); sqlite> INSERT INTO foo VALUES (3, 232,'2008-01-01','2008-01-10','it','trans'); sqlite> INSERT INTO foo VALUES (4, 232,'2008-01-01','2008-01-10','es','val'); sqlite> INSERT INTO foo VALUES (5, 232,'2008-01-01','2008-01-10','fr','val'); sqlite> INSERT INTO foo VALUES (6, 232,'2008-01-01','2008-01-10','it','val'); sqlite> 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) 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-10|2008-01-10|it|val sqlite> The UPDATE statement above seems to do what you want. Convert that to a TRIGGER if you so want, but realize that the TRIGGER is supposed to, well, trigger on some event such as UPDATE or INSERT or DELETE. Other than that, the above should get you going. -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
