SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 splitting records in a column and updating
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

srgade82
Starting Member

USA
4 Posts

Posted - 12/03/2010 :  12:44:18  Show Profile  Reply with Quote
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
1770 Posts

Posted - 12/03/2010 :  13:46:27  Show Profile  Reply with Quote
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)
Go to Top of Page

srgade82
Starting Member

USA
4 Posts

Posted - 12/03/2010 :  15:53:09  Show Profile  Reply with Quote
starting from the first record is 1 , 2,..till.3000.


sagar
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1770 Posts

Posted - 12/03/2010 :  17:40:23  Show Profile  Reply with Quote
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)
Go to Top of Page

srgade82
Starting Member

USA
4 Posts

Posted - 12/03/2010 :  19:35:37  Show Profile  Reply with Quote

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

srgade82
Starting Member

USA
4 Posts

Posted - 12/04/2010 :  13:52:49  Show Profile  Reply with Quote
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
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 12/05/2010 :  10:54:13  Show Profile  Reply with Quote
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

Go to Top of Page

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 12/06/2010 :  06:32:08  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.52 seconds. Powered By: Snitz Forums 2000