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
 General SQL Server Forums
 New to SQL Server Programming
 incremented updated

Author  Topic 

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-07-29 : 05:19:48
hi
whats the best way to do this
For 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 #X
VALUES('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 MyUpdates
AS
(
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 table
SET SecurityID = NewVal + '-' + CAST(R AS VArchar(5)) -- i added a dash to differentiate from the date
FROM #X A
INNER JOIN MyUpdates B ON A.ID = B.ID
WHERE InvestmentType = 'Cash'


SELECT * FROM #X
Go to Top of Page

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 -- a

CREATE TABLE #X
(
ID int Identity(1,1),
SecurityID varchar(100),
PeriodEndDate date,
InvestmentType varchar(10)

)



INSERT INTO #X
VALUES('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 MyUpdates
AS
(
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
WHERE InvestmentType = 'Cash' -- forgot this line
)

UPDATE #X -- or Update alias A -- i prefer naming the target table
SET SecurityID = NewVal + '-' + CAST(R AS VArchar(5)) -- i added a dash to differentiate from the date
FROM #X A
INNER JOIN MyUpdates B ON A.ID = B.ID
WHERE InvestmentType = 'Cash'


SELECT * FROM #X
Go to Top of Page

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.
Go to Top of Page

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 8
Invalid column name 'NewVal'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'R'.



do i have to declare the column names
here 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 records
FROM dbo.BNYPortValInbound
WHERE InvestmentType = 'Cash' -- forgot this line


UPDATE dbo.BNYPortValInbound -- or Update alias A -- i prefer naming the target table
SET SecurityID = NewVal + '-' + CAST(R AS VArchar(5)) -- i added a dash to differentiate from the date
FROM dbo.BNYPortValInbound
WhERE PK_ID = PK_ID
AND InvestmentType = 'Cash'
Go to Top of Page

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 MyUpdates
AS
(
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 records
FROM dbo.BNYPortValInbound
WHERE InvestmentType = 'Cash'

)

UPDATE dbo.BNYPortValInbound -- or Update alias A -- i prefer naming the target table
SET SecurityID = NewVal + '-' + CAST(R AS VArchar(5)) -- i added a dash to differentiate from the date
FROM 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 CTE


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 records
INTO #MyUpdates -- quick Temp Table
FROM dbo.BNYPortValInbound
WHERE InvestmentType = 'Cash' -- forgot this line


UPDATE dbo.BNYPortValInbound -- or Update alias A -- i prefer naming the target table
SET SecurityID = NewVal + '-' + CAST(R AS VArchar(5)) -- i added a dash to differentiate from the date
FROM 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.
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-07-30 : 09:05:20
thanks for that working now

Go to Top of Page
   

- Advertisement -