SQL Update with join

[jayman9782]

Someone please help. I'm running the following SQL query in sql server 2000...

UPDATE S
SET S.[Dept Request] = B.[BY Request]
FROM [Budget Details - 2007] B LEFT OUTER JOIN
[SCI Transaction Summary] S ON B.[Account Key] = S.Account
WHERE (S.[Year] = 2007)

All it does is zero out the field I'm trying to update. I'm almost positive the syntax is 100% correct, but then again it is nearing the end of the day...
Thanks for any help!

[ck9663]

Someone please help. I'm running the following SQL query in sql server 2000...

UPDATE S
SET S.[Dept Request] = B.[BY Request]
FROM [Budget Details - 2007] B LEFT OUTER JOIN
[SCI Transaction Summary] S ON B.[Account Key] = S.Account
WHERE (S.[Year] = 2007)

All it does is zero out the field I'm trying to update. I'm almost positive the syntax is 100% correct, but then again it is nearing the end of the day...
Thanks for any help!

Try this:

UPDATE S
SET  [Dept Request] = B.[BY Request]
FROM [Budget Details - 2007] B 
LEFT OUTER JOIN [SCI Transaction Summary] S ON B.[Account Key] = S.Account
WHERE     (S.[Year] = 2007)

-- CK

[jayman9782]

Thanks for the quick response CK...but that was what I originally tried. It had the same results as the query i posted. any other suggestions?

[ck9663]

What's your error again?

-- CK

[jayman9782]

It does not create an error, rather it updates the [Dept Request] field to 0 where the inner join is true and <null> where the inner join is false. It should be updating this field to the value of [BY Request] from the joined table.

[ck9663]

Do a distinct value on the [BY Request] and check the values.

-- CK

[jayman9782]

I actually tried that as well...and ran a select query on the tables just to make sure the values were being picked up correctly. All seemed well. The frustration and time wasted got the better of me...I cheated by exporting the data to an access mdb, making the conversions and re-importing the data back into the sql server. This doesn't mean i wouldn't like to figure this out if you have any other suggestions. thanks.
-Jay

[ck9663]

try this:

UPDATE [SCI Transaction Summary]
SET  [Dept Request] = B.[BY Request]
FROM [SCI Transaction Summary] 
LEFT OUTER JOIN [Budget Details - 2007] B 
 ON B.[Account Key] = [SCI Transaction Summary].Account
and [SCI Transaction Summary].[Year] = 2007
 

-- CK