Update Query syntax problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Query syntax problem

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.RouteNbr

All column names are correct.
There are 3 tables:
T1 = SubJob PK = JobSubNbr
T2 = Route PK = RouteNbr & FK = JobSubNbr
T3 = EmployeeTime FK = RouteNbr

I want to update the JobCost column in T1 with the Sum of EmployeeCharges in T3 which are assoicate to Routes with that SubJob

SubJobs have 1 to many Routes
Routes have 1 to many EmployeeTimes

Why 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 T2
ON T1.JobSubNbr = T2.JobSubNbr 
INNER JOIN EmployeeTime T3
ON 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources