Author |
Topic |
Rmadler
Starting Member
3 Posts |
Posted - 2010-02-01 : 14:46:56
|
Hello,I am trying to update a table using 2 inner joins.I am getting a syntax error with the following:Update T1 set T1.JobCost = (Select Sum(T3.EmployeeCharges) From SubJob T1 INNER JOIN Route T2 ON T1.JobSubNbr = T2.JobSubNbr INNER JOIN EmployeeTime T3 ON T2.RouteNbr = T3.RouteNbrAll column names are correct.There are 3 tables:T1 = SubJob PK = JobSubNbrT2 = Route PK = RouteNbr & FK = JobSubNbrT3 = EmployeeTime FK = RouteNbrI want to update the JobCost column in T1 with the Sum of EmployeeCharges in T3 which are assoicate to Routes with that SubJobSubJobs have 1 to many RoutesRoutes have 1 to many EmployeeTimesWhy am I getting a syntax error? |
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-02-01 : 14:54:35
|
The error message would be helpful for us. No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-02-01 : 16:03:54
|
Just a guess:Update T1 set T1.JobCost = (Select Sum(T3.EmployeeCharges)From SubJob T1 INNER JOIN Route T2ON T1.JobSubNbr = T2.JobSubNbr INNER JOIN EmployeeTime T3ON T2.RouteNbr = T3.RouteNbr |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-02-01 : 16:06:02
|
I thought that too.But I was not in the mood... No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-02-01 : 16:22:07
|
All good Webfred.. You gotta leave some low-hanging fruit for the rest of us! :) |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-02-01 : 16:27:26
|
yes - I am too kind (like kristen said recently) No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-01 : 16:43:24
|
Can't have an aggregate in an UPDATE can you? (unless you nest it ... and then you have to deal with joining that back to whatever table you are updating)This maybe?Update T1 set T1.JobCost =( Select Sum(T3.EmployeeCharges) FROM Route T2 INNER JOIN EmployeeTime T3 ON T2.RouteNbr = T3.RouteNbr WHERE T2.JobSubNbr = T1.JobSubNbr)From SubJob T1 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-02-01 : 16:48:36
|
we talk about it one second and then we do it kristen's way No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
Rmadler
Starting Member
3 Posts |
Posted - 2010-02-06 : 14:04:31
|
This solution works fine as long as all joins return a row.If the Inner Join does not return a row, it updates JobCost with a NULL. In this situation, I want to do nothing or add 0 to the existing JobCost. HELP! Please. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-02-06 : 14:15:31
|
Try ISNULL(Sum(T3.SubContratorTotal),0) No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|