Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

row_number replacement Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2012 2:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 2:07 PM
Points: 171, Visits: 556
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
Post #1364924
Posted Wednesday, September 26, 2012 3:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's 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)
Post #1364959
Posted Thursday, September 27, 2012 7:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 11:58 AM
Points: 212, Visits: 1,040
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.
Post #1365225
Posted Thursday, September 27, 2012 7:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Post #1365249
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse