Update using Row_Number - 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 2005 Forums

Transact-SQL (2005)

Update using Row_Number

Author

Topic

bbowser
Starting Member

43 Posts

Posted - 2010-01-11 : 00:02:41

I'm trying to update a single table with the following but I'm experiencing some difficulty with the row number. The code is:

Update tblIncome
Set IncomeAmount1 = '22000'
,IncomeAmount2 = '0'
,IncomeAmount3 = '0'
,IncomeAmount4 = '12'
,IncomeAmount5 = '240'
,IncomeAmount6 = '0'
,IncomeAmount7 = '99'
From (Select Row_Number() Over(Order by ApplicantID)AS RowID, ApplicantID, IncomeAmount1, IncomeAmount2
,IncomeAmount3, IncomeAmount4, IncomeAmount5, IncomeAmount6, IncomeAmount7 From tblIncome
Where ApplicantID = '1608' and RowID = '2')

What am I doing wrong? What would is the correct or easy way to do this? Thanks in advance for your help.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-11 : 00:08:17

Try this,

Update a
Set IncomeAmount1 = '22000'
,IncomeAmount2 = '0'
,IncomeAmount3 = '0'
,IncomeAmount4 = '12'
,IncomeAmount5 = '240'
,IncomeAmount6 = '0'
,IncomeAmount7 = '99'
From (Select Row_Number() Over(Order by ApplicantID)AS RowID, ApplicantID, IncomeAmount1, IncomeAmount2
,IncomeAmount3, IncomeAmount4, IncomeAmount5, IncomeAmount6, IncomeAmount7 From tblIncome
Where ApplicantID = '1608') a where RowID = 2

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

bbowser
Starting Member

43 Posts

Posted - 2010-01-11 : 00:50:50

I think that's got it!! Thank you very much Seth! You're a genius!

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-11 : 00:59:47

quote:
Originally posted by bbowser

I think that's got it!! Thank you very much Seth! You're a genius!

Not at all, anyway thanks!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources