Greetings!
I have this table
CREATE TABLE LSOpenJobs
(
id integer primary key,
ProjID integer,
PSubClass,
lang,
ProjFund,
RateType
);
Imagine this set of records...
171421|132959|DOC-Trans|DE-DE|860.69|PER-WORD
171422|132959|DOC-Trans|ES-LA|624.96|PER-WORD
171423|132959|DOC-Trans|IT-IT|712.28|PER-WORD
171424|132959|DOC-Trans|PT-BR|738.91|PER-WORD
171425|132959|File-Proc|DE-DE|10.63|HOURS
171426|132959|File-Proc|ES-LA|10.63|HOURS
171427|132959|File-Proc|IT-IT|10.63|HOURS
171428|132959|File-Proc|PT-BR|10.63|HOURS
171429|132959|PM|DE-DE|116.86|10%
171430|132959|PM|ES-LA|96.91|10%
171431|132959|PM|IT-IT|102.02|10%
171432|132959|PM|PT-BR|119.55|10%
171433|132959|Trans-Create|DE-DE|297.28|HOURS
171434|132959|Trans-Create|ES-LA|333.52|HOURS
171435|132959|Trans-Create|IT-IT|297.28|HOURS
171436|132959|Trans-Create|PT-BR|445.92|HOURS
171437|132959|QuoteAppr|DE-DE ES-LA IT-IT PT-BR||HOURS
171438|132959|XTranslate|DE-DE ES-LA IT-IT PT-BR||HOURS
171439|132959|Delivery|DE-DE ES-LA IT-IT PT-BR||HOURS
171440|132959|Q-Notes|DE-DE ES-LA IT-IT PT-BR||Deliverable
171932|132959|Validation|DE-DE||PER-WORD
171933|132959|Validation|ES-LA||PER-WORD
171934|132959|Validation|IT-IT||PER-WORD
171935|132959|Validation|PT-BR||PER-WORD
172979|132959|TTX-Update|DE-DE|0.00|PER-WORD
What I would like to do is to Update the ProjFund of all PM PSubClass
automatically. I have a two step process, but I am wondering if there is a way
to do this without the two steps or every time I update one of these records.
these are my two steps:
1. SELECT RateType FROM LSOpenJobs WHERE ProjID=132959 AND PSubClass='PM' AND
lang='DE-DE';
2. Use the RateType, let's say it's 10%, to create a this update
BEGIN;
UPDATE LSOpenJobs SET ProjFund =
(
SELECT round(sum(ProjFund) * 0.20,2) FROM LSOpenJobs
WHERE
ProjID = 132959 AND
lang = 'DE-DE' AND
PSubClass != 'PM'
)
WHERE
ProjID = 132959 AND lang = 'DE-DE' AND RateType = '10%' AND PSubClass =
'PM';COMMIT TRANSACTION;
So, is there any way to kill this two-step process into one, or even better,
create a trigger for PM that have "%" on the RateType? Any help would be
greatly appreciated. Thanks.
jos?
