splitting records in a column and updating

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

SQL Server 2005 Forums

Transact-SQL (2005)

splitting records in a column and updating

Author

Topic

srgade82
Starting Member

4 Posts

Posted - 2010-12-03 : 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
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-12-03 : 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

4 Posts

Posted - 2010-12-03 : 15:53:09

starting from the first record is 1 , 2,..till.3000.

sagar

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-12-03 : 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

4 Posts

Posted - 2010-12-03 : 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

4 Posts

Posted - 2010-12-04 : 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

2937 Posts

Posted - 2010-12-05 : 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 - 2010-12-06 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources