Upate Question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Upate Question

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-01-22 : 19:48:07

I have the following table:

ACCOUNT|AccountFeeLy|Month
12345 | 1000.00 | 11-1-2006
12355 | 1001.00 | 11-1-2006
12345 | 2423.00 | 12-1-2006
12355 | 1232.00 | 12-1-2006

I want to update the Accountfeely in dec2006 to reflect the november 2006 #, not sure how to go about writing the update statement, there are 20K records that I need to do this to, and need to match the accounts numbers.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-22 : 20:45:20

if i understand u correctly...

UPDATE TableName
SET AccountFeeLy = b.AccountFeeLy
FROM TableName a
JOIN (
SELECT Account, AccountFeeLy
FROM TableName
WHERE month = '11-1-2006'
) b
On a.Account = b.Account
where a.Month = '12-1-2006'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-22 : 20:45:42

you mean this ?


update d
set AccountFeeLy = n.AccountFeely
from tbl d inner join tbl n
on   d.Account = n.Account
and  d.Month   = '20061201'
and  n.Month   = '20061101'

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-01-22 : 20:54:22

I cant thank you two enough, it appears the solution from russell worked, I appreciate the advice from both of you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-22 : 20:55:45

duhaas, what's wrong with my query ? syntax error ?

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-01-22 : 20:58:43

my apologizes, I am going to assume nothing, I saw his post first and than tried it first and it worked, didn't mean not to give you any credit. in looking @ it, although not a sql expert, it appears it would work, and its less code

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-22 : 21:11:51

quote:
Originally posted by duhaas

my apologizes, I am going to assume nothing, I saw his post first and than tried it first and it worked, didn't mean not to give you any credit. in looking @ it, although not a sql expert, it appears it would work, and its less code

I am not concerning about credit. Anyway i am not expert myself, still i am learning everyday. My concern is where is the problem, if it is not working.

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-01-22 : 21:49:33

sorry, must be some confusion, everything is working as expected

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-22 : 21:51:36

thank you for the feedback

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-22 : 22:56:24

kh, yours is better (in my opinion), i started to post that way and had a syntax error (was trying to update table name instead of alias), so i just did it the lazy way. both work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources