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

Quote:

Originally Posted by 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!

Try this:

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

 
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

SQL Update with join

re: SQL Update with join

re: SQL Update with join

re: SQL Update with join

re: SQL Update with join

re: SQL Update with join

re: SQL Update with join

re: SQL Update with join

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about