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.
Author |
Topic |
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2014-07-29 : 05:19:48
|
hiwhats the best way to do thisFor all records where Investment Type = ‘Cash’, perform the following update:Update the Security ID field with Security ID + Period End Date + n where n is 1 for first record and incremented by 1 for each subsequent record. |
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2014-07-29 : 07:18:03
|
CREATE TABLE #X(ID int Identity(1,1),SecurityID varchar(100),PeriodEndDate date,InvestmentType varchar(10))INSERT INTO #XVALUES('AAA','7/1/2014','Cash'),('BBB','7/1/2014','Cash'),('CCC','7/2/2014','Cash'),('DDD','7/3/2014','FixedAsset'),('EEE','7/4/2014','Cash')SELECT * FROM #X;With MyUpdatesAS(SELECT ID,SecurityID + CAST(PeriodEndDate as varchar(10)) NewVal, ROW_NUMBER() OVER ( ORDER BY PeriodEndDate ) R-- define some order for which you consider the first and subsequent records FROM #X) UPDATE #X -- or Update alias A -- i prefer naming the target tableSET SecurityID = NewVal + '-' + CAST(R AS VArchar(5)) -- i added a dash to differentiate from the dateFROM #X A INNER JOIN MyUpdates B ON A.ID = B.IDWHERE InvestmentType = 'Cash'SELECT * FROM #X |
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2014-07-29 : 07:28:29
|
As I went to get coffee, I realized the Investment type Cash was an after thought when I throw this together.. I added a line to correct -- aCREATE TABLE #X(ID int Identity(1,1),SecurityID varchar(100),PeriodEndDate date,InvestmentType varchar(10))INSERT INTO #XVALUES('AAA','7/1/2014','Cash'),('BBB','7/1/2014','Cash'),('CCC','7/2/2014','Cash'),('DDD','7/3/2014','FixedAsset'),('EEE','7/4/2014','Cash')SELECT * FROM #X;With MyUpdatesAS(SELECT ID,SecurityID + CAST(PeriodEndDate as varchar(10)) NewVal, ROW_NUMBER() OVER ( ORDER BY PeriodEndDate ) R-- define some order for which you consider the first and subsequent recordsFROM #XWHERE InvestmentType = 'Cash' -- forgot this line) UPDATE #X -- or Update alias A -- i prefer naming the target tableSET SecurityID = NewVal + '-' + CAST(R AS VArchar(5)) -- i added a dash to differentiate from the dateFROM #X AINNER JOIN MyUpdates B ON A.ID = B.IDWHERE InvestmentType = 'Cash'SELECT * FROM #X |
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2014-07-30 : 06:29:48
|
thanks very much will try this out and see how i get on. |
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2014-07-30 : 07:00:39
|
i change it around a bit to suit my table but an getting erros saying Msg 207, Level 16, State 1, Line 8Invalid column name 'NewVal'.Msg 207, Level 16, State 1, Line 8Invalid column name 'R'. do i have to declare the column nameshere is the way the code is now SELECT PK_ID,SecurityID + CAST(PeriodEndDate as varchar(20)) as NewVal, ROW_NUMBER() OVER ( ORDER BY PeriodEndDate ) as R-- define some order for which you consider the first and subsequent recordsFROM dbo.BNYPortValInboundWHERE InvestmentType = 'Cash' -- forgot this lineUPDATE dbo.BNYPortValInbound -- or Update alias A -- i prefer naming the target tableSET SecurityID = NewVal + '-' + CAST(R AS VArchar(5)) -- i added a dash to differentiate from the dateFROM dbo.BNYPortValInbound WhERE PK_ID = PK_IDAND InvestmentType = 'Cash' |
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2014-07-30 : 08:41:29
|
Your code doesn't seem to be using a CTE;With MyUpdatesAS(SELECT PK_ID,SecurityID + CAST(PeriodEndDate as varchar(20)) as NewVal, ROW_NUMBER() OVER ( ORDER BY PeriodEndDate ) as R-- define some order for which you consider the first and subsequent recordsFROM dbo.BNYPortValInboundWHERE InvestmentType = 'Cash' ) UPDATE dbo.BNYPortValInbound -- or Update alias A -- i prefer naming the target tableSET SecurityID = NewVal + '-' + CAST(R AS VArchar(5)) -- i added a dash to differentiate from the dateFROM dbo.BNYPortValInbound A INNER JOIN MyUpdates B ON A.PK_ID = B.PK_ID WHERE IA.nvestmentType = 'Cash'alternatively, you can create a temp table rather than use a CTESELECT PK_ID,SecurityID + CAST(PeriodEndDate as varchar(20)) as NewVal, ROW_NUMBER() OVER ( ORDER BY PeriodEndDate ) as R-- define some order for which you consider the first and subsequent recordsINTO #MyUpdates -- quick Temp TableFROM dbo.BNYPortValInboundWHERE InvestmentType = 'Cash' -- forgot this lineUPDATE dbo.BNYPortValInbound -- or Update alias A -- i prefer naming the target tableSET SecurityID = NewVal + '-' + CAST(R AS VArchar(5)) -- i added a dash to differentiate from the dateFROM dbo.BNYPortValInbound A INNER JOIN #MyUpdates B ON A.PK_ID = B.PK_ID -- join on the temp table WHERE IA.nvestmentType = 'Cash'The MyUpdates CTE is being used as a sort of in memory view. It is select the data and providing the numbering you need. You then join with your table(dbo.BNYPortValInbound ) with that in memory view (MyUpdates) so you have access to the values you want to use as the source of your update. Without the join, you cannot access the values. You can also use a temp table as I have included. |
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2014-07-30 : 09:05:20
|
thanks for that working now |
|
|
|
|
|
|
|