By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,147 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

SQL Update with join

P: 10
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!
Apr 3 '08 #1
Share this Question
Share on Google+
7 Replies


ck9663
Expert 2.5K+
P: 2,878
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
  1. UPDATE S
  2. SET  [Dept Request] = B.[BY Request]
  3. FROM [Budget Details - 2007] B 
  4. LEFT OUTER JOIN [SCI Transaction Summary] S ON B.[Account Key] = S.Account
  5. WHERE     (S.[Year] = 2007)

-- CK
Apr 3 '08 #2

P: 10
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?
Apr 4 '08 #3

ck9663
Expert 2.5K+
P: 2,878
What's your error again?

-- CK
Apr 4 '08 #4

P: 10
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.
Apr 4 '08 #5

ck9663
Expert 2.5K+
P: 2,878
Do a distinct value on the [BY Request] and check the values.

-- CK
Apr 4 '08 #6

P: 10
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
Apr 5 '08 #7

ck9663
Expert 2.5K+
P: 2,878
try this:

Expand|Select|Wrap|Line Numbers
  1. UPDATE [SCI Transaction Summary]
  2. SET  [Dept Request] = B.[BY Request]
  3. FROM [SCI Transaction Summary] 
  4. LEFT OUTER JOIN [Budget Details - 2007] B 
  5.  ON B.[Account Key] = [SCI Transaction Summary].Account
  6. and [SCI Transaction Summary].[Year] = 2007
  7.  
-- CK
Apr 5 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.