| Author |
 Topic  |
|
|
srgade82
Starting Member
USA
4 Posts |
 Posted - 12/03/2010 : 12:44:18
|
HI
I have a obj_key (varchar) column which has around 3000 records identical
i need to update the records after 997 rows. the 998th record should be updated by (obj_key+0001) till the next 997 rows ie (till 1994th record) after next 997 records the column should be updated by(obj_key+0002)....so on till the records end.
Can any one help me on how to do this.
Thanks in advance
sagar |
|
|
Bustaz Kool
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 12/03/2010 : 13:46:27
|
How are you imposing an order on the records or, more specifically, how do you determine which record is the first, second, 997th, 998th, etc?
=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986) |
 |
|
|
srgade82
Starting Member
USA
4 Posts |
Posted - 12/03/2010 : 15:53:09
|
starting from the first record is 1 , 2,..till.3000.
sagar |
|
|
|
Bustaz Kool
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 12/03/2010 : 17:40:23
|
1 what? 2 what? 3000 what?
All you've said is that the first one is first, the second one is second. If I looked at the data, what column and value would lead me to know that this record was first? Since the column/records are identical, how does a person determine their order?
=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986) |
|
|
|
srgade82
Starting Member
USA
4 Posts |
Posted - 12/03/2010 : 19:35:37
|
Table
column 1 column2
1 abc
2 sass
3 sdds
4 sgsg
. .
. .
after 997 records column2 record should be added by 0001. the next 997 column records look like xyz0001,fsdd0001,dsds0001....after 997 records the column records should by updated by 0002, they look like for example fdfd0002,fdrr0002....
sagar |
|
|
|
srgade82
Starting Member
USA
4 Posts |
Posted - 12/04/2010 : 13:52:49
|
sorry it looks this way
Table
column 1 column2
1 abc
2 abc
3 abc
4 abc
. .
. .
after 997 records column2 record should be added by 0001. the next 997 column records look like xyz0001,fsdd0001,dsds0001....after 997 records the column records should by updated by 0002, they look like for example fdfd0002,fdrr0002....
sagar |
|
|
|
Sachin.Nand
Flowing Fount of Yak Knowledge
2937 Posts |
Posted - 12/05/2010 : 10:54:13
|
For sake of testing run the query below
SELECT NUMBER
INTO #tbl
FROM MASTER..spt_values
WHERE TYPE = 'p'
AND NUMBER >= 1
SELECT *
FROM #tbl;
WITH cte
AS (SELECT MIN(NUMBER) MIN,
MAX(NUMBER) MAX,
Row_number()OVER(ORDER BY (SELECT 1))rid
FROM #tbl
GROUP BY NUMBER / 998)
UPDATE t
SET t.NUMBER = t.NUMBER + rid
FROM #tbl t
INNER JOIN (SELECT rid,
NUMBER
FROM cte
CROSS APPLY(SELECT NUMBER
FROM #tbl t
WHERE t.NUMBER BETWEEN MIN AND MAX)t)t1
ON t1.NUMBER = t.NUMBER
SELECT *
FROM #tbl
DROP TABLE #tbl
PBUH
|
|
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 12/06/2010 : 06:32:08
|
declare @sample1 table
(
col1 int identity(1,1) not null,
col2 varchar(100)
)
declare @sample2 table
(
col1 int identity(1,1) not null,
col2 varchar(100)
)
insert into @sample1(col2)
select top 1000 left(ltrim(m1.name),5) from
master..spt_values m1
cross join
master..spt_values m2
where m1.name is not null
insert into @sample2(col2)
select top 1000 left(ltrim(m1.name),5) from
master..spt_values m1
cross join
master..spt_values m2
where m1.name is not null
ORDER BY M1.NAME DESC
declare @tab table
(
col1 int ,
col2 varchar(100)
)
insert into @tab
select * from @sample1
union all
select * from @sample2
update @tab
set col2 = col2 + case when col1 >996
then
Replicate(0,5-DataLength(CONVERT(varchar(100),cast(col1-996 AS int)))) + CONVERT(varchar(100),cast(col1-996 AS int)) else SPACE(0) end
select * from @tab
order by col1 desc
Iam a slow walker but i never walk back |
|
|
| |
Topic |
|
splitting records in a column and updating
