SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


row_number replacement


row_number replacement

Author
Message
komal145
komal145
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 Visits: 1045
Hi friends,



I have created temp table with row_number() since i need the latest loanumber with step

Create Table #Max_step
(
Loan_number varchar(10),
FID Int,
Step int,
Modifieddate datetime,
ROW_id int
)

Create table Tableb
(loan_number varchar(10),
step int,
Description varchar(20))

Insert into #Max_step values('91746',1110,2,'2010-07-31 19:34:35.000',2)
Insert into #Max_step values('91746',1120,1,'2010-07-31 19:30:35.000',3)
Insert into #Max_step values('91746',1120,3,'2010-07-31 19:30:35.000',1)


Insert into Tableb values('91746',1,'description1')
Insert into Tableb values('91746',2,'description2')


I am trying to update a column in TableB based on the row_id =2 from max_step

but every time i check the target column value changes ..example
updating Column in tableC where max_step.row_id=2 joined with tableB on step and loanumber.

UPDATE TableC
SET step_desc= Tableb.StepDescription
FROM #MAX_STEP MAX_STEP WITH (NOLOCK)
JOIN Tableb B WITH (NOLOCK) ON MAX_STEP.LoanNumber = b.LoanNumber
and MAx_step.Step=b.step

WHERE MAX_STEP.Row_ID = 2

Can Anyone tell me whats the best way to replace row_number() while getting data into max_step table
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26143 Visits: 17539
Can you ddl and sample data for TableC? Also, a clear explanation what you are trying would help. I am not sure I understand your requirements.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
brendan woulfe
brendan woulfe
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 Visits: 1087
I don't see what's wrong with your query other than you don't have a loannumber column. It is loan_number.

Post the ddl and sample data for TableC and give expected output and someone should be able to help.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16272 Visits: 19551
komal145 (9/26/2012)
...
UPDATE TableC
SET step_desc= Tableb.StepDescription
FROM #MAX_STEP MAX_STEP WITH (NOLOCK)
JOIN Tableb B WITH (NOLOCK) ON MAX_STEP.LoanNumber = b.LoanNumber
and MAx_step.Step=b.step

WHERE MAX_STEP.Row_ID = 2...


This is an unrestricted update. Every row in tableC will be updated to the same value of step_desc, because TableC isn't referenced in the FROM list. Since it's a trivial matter to convert a SELECT...FROM into an UPDATE...FROM, why don't you write it and post it here? What you want is a query which will return step_desc from TableC and Tableb.StepDescription.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search