Update sequence - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Update sequence

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2008-01-15 : 13:47:34

Currently I have got a table showing something like this:

1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 3 1
7 4 1
8 4 2

I need to update the sequence field so the result would be

1 1 1
2 1 6
3 1 11
4 2 1
5 2 6
6 3 11
7 4 1
8 4 6



drop table [dbo].[#tblCursosEstructura]

CREATE TABLE [dbo].[#tblCursosEstructura] (
	[Cus_Id] [int] IDENTITY (1, 1) NOT NULL ,
	[Cus_Cu_Id] [int] NULL ,
	[Cus_Order] [int] NULL 
) 
GO
INSERT INTO #tblCursosEstructura (Cus_Cu_Id, Cus_Order)
Values (1,1)
INSERT INTO #tblCursosEstructura (Cus_Cu_Id, Cus_Order)
Values (1,2)
INSERT INTO #tblCursosEstructura (Cus_Cu_Id, Cus_Order)
Values (1,3)
INSERT INTO #tblCursosEstructura (Cus_Cu_Id, Cus_Order)
Values (2,1)
INSERT INTO #tblCursosEstructura (Cus_Cu_Id, Cus_Order)
Values (2,2)
INSERT INTO #tblCursosEstructura (Cus_Cu_Id, Cus_Order)
Values (3,1)
INSERT INTO #tblCursosEstructura (Cus_Cu_Id, Cus_Order)
Values (4,1)
INSERT INTO #tblCursosEstructura (Cus_Cu_Id, Cus_Order)
Values (4,2)

jean-luc
www.corobori.com

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-01-15 : 14:19:24

[code]UPDATE #tblCursosEstructura
SET Cus_Order = CASE Cus_Order WHEN 2 THEN 6 WHEN 3 THEN 11 ELSE Cus_Order END[/code]

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-01-15 : 14:33:14

it wont work for row_num 6 don.

whats the relation between two sequences

raaj
Posting Yak Master

129 Posts

Posted - 2008-01-15 : 17:01:47

Hi,
I tried in this way...looks like the same ouptput which u want...
but thr might also be some other technique which wud be simpler than this...just try this first....

update #tblCursosEstructura
set cus_order = case when cus_id =1 then 1
when cus_id =2 then 6
when cus_id =3 then 11
when cus_id =4 then 1
when cus_id =5 then 6
when cus_id =6 then 11
when cus_id =7 then 1
when cus_id =8 then 6
end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-16 : 03:18:15

Do

select *,case when Cus_Id%3=0 then 11 when Cus_Id%3=1 then 1 else 6 end from #tblCursosEstructura

If last column gives what you want then

UPDATE #tblCursosEstructura
SET Cus_Order = case when Cus_Id%3=0 then 11 when Cus_Id%3=1 then 1 else 6 end

Madhivanan

Failing to plan is Planning to fail

Corobori
Posting Yak Master

105 Posts

Posted - 2008-01-18 : 13:45:06

It sounds like I didn't explain my question properly.
Those date are excerpt of sample data where there are hundred of rows therefore a case on cus_id is not an option. Cus_Id is only a sequential identifier.
Cus_Cu_Id (actually lesson's number) and Cus_Order (representing steps ) are involved. I want to resecuence (Update) the Cus_Order from 1 by step of 5 for each Cus_Cu_Id.
I reckon I might have publish this message in the wrong forum Transact-SQL (2000) was perhaps better.

jean-luc
www.corobori.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-18 : 22:41:06

quote:
Originally posted by Corobori

It sounds like I didn't explain my question properly.
Those date are excerpt of sample data where there are hundred of rows therefore a case on cus_id is not an option. Cus_Id is only a sequential identifier.
Cus_Cu_Id (actually lesson's number) and Cus_Order (representing steps ) are involved. I want to resecuence (Update) the Cus_Order from 1 by step of 5 for each Cus_Cu_Id.
I reckon I might have publish this message in the wrong forum Transact-SQL (2000) was perhaps better.

jean-luc
www.corobori.com

Try like this:-

UPDATE ce
SET ce.Cus_Order=(5 * tmp.SNo) + 1
FROM [#tblCursosEstructura] ce
INNER JOIN
( SELECT [Cus_Id], 
         [Cus_Cu_Id],
         (  SELECT COUNT(*) 
            FROM [#tblCursosEstructura] 
            WHERE [Cus_Cu_Id]=t.[Cus_Cu_Id] 
            AND [Cus_Id]<t.[Cus_Id]
         ) AS SNo
  FROM [#tblCursosEstructura] t
)tmp
ON tmp.[Cus_Id]=ce.[Cus_Id]
AND tmp.[Cus_Cu_Id]=ce.[Cus_Cu_Id]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources