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

List:       sqlite-users
Subject:    Re: [sqlite] Trigger UPDATE based on a different row
From:       "jose isaias cabrera" <cabrera () wrc ! xerox ! com>
Date:       2008-12-22 6:03:58
Message-ID: 55460EEA1BB8400EBAF491997583D5AD () stso ! mc ! xerox ! com
[Download RAW message or body]

"jose isaias cabrera" wrote...

>
> "P Kishor" wrote...
>
>> On 12/20/08, jose isaias cabrera <cabrera@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.
>>
>
> Thanks, Puneet.  I can work with this.
>
> josé

Puneet,

thanks for the help.  i have one more check to do: If the bdate of the 'val' 
job is greater than the 'trans' job, then the val job should stay the same. 
So, I ran this UPDATE:

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';
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, line 6 has lost the bdate.  I did some searches on the 
internet to try to find out how to get it to work, but could not figure it 
out.  Newbie, of course.

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

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