Update Statement Confusion - 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

General SQL Server Forums

New to SQL Server Programming

Update Statement Confusion

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-10-10 : 16:16:13

I have a table that looks likething like:

identity_column  fcpoptext  fnorder
100000            FED EX     1
100001            DHL        2
100002            UPS        3

What I need to do is to re-order the fnorder as consecutive nubmers starting with 0 based on the alphabetical order of fcpoptext.

I can do update statements, but I don't really know how to do so incrementally.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-10 : 16:57:40

Hello David,

maybe like this (sql server 2005):
create table #sample(
identity_column int,
fcpoptext varchar(10),
fnorder int
)
insert #sample
select 100000, 'FED EX', 1 union
select 100001, 'DHL', 2 union
select 100002, 'UPS', 3

-- this select shows, how to get the wanted value
select
identity_column,
fcpoptext,
fnorder,
row_number() over (order by fcpoptext)-1 as new_fnorder
from #sample

drop table #sample

Greetings
Webfred

Planning replaces chance by mistake

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-10-10 : 16:58:23

Thanks Fred. I'm sorry, but I should have mentioned that I am on SQL 2000.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-10 : 17:12:48

maybe it's possible to take first a temp table and the use it to update?

create table #sample(
identity_column int,
fcpoptext varchar(10),
fnorder int
)
insert #sample
select 100000, 'FED EX', 1 union
select 100001, 'DHL', 2 union
select 100002, 'UPS', 3

-- this select shows, how to get the wanted value (2005)
select
identity_column,
fcpoptext,
fnorder,
row_number() over (order by fcpoptext)-1 as new_fnorder
from #sample
---- this select-into shows, how to get the wanted value (2000)
select
identity_column,
fcpoptext,
identity(int,0,1) as new_fnorder_2000,
fnorder
into #new_sample
from #sample
order by fcpoptext

select * from #new_sample

drop table #sample
drop table #new_sample

Fred

Planning replaces chance by mistake

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-11 : 01:04:45

[code]UPDATE t
SET t.fnorder=(select count(*) from YourTable WHERE fcpoptext<t.fcpoptext)
FROM YourTable t[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 03:47:21

quote:
Originally posted by sql1_learner

sdfssgsdgsd

Touché!

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 03:47:43

quote:
Originally posted by sql1_learner

sdfssgsdgsd

what does that mean?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 03:49:36

If you have the clustered index over fcPopText, this will be really fast for many records

DECLARE	@Index INT

SET @Index = -1

UPDATE	t1
SET	t1.fnOrder = t1.fnOrder = @Index + 1
FROM	Table1 AS t1 WITH (TABLOCK, INDEX(0))

E 12°55'05.63"
N 56°04'39.26"

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-10-13 : 09:06:37

quote:

Originally posted by visakh16

UPDATE t
SET t.fnorder=(select count(*) from YourTable WHERE fcpoptext<t.fcpoptext)
FROM YourTable t

Amazing, that worked perfectly. Thanks again Visahk.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 09:31:01

quote:
Originally posted by DavidChel

quote:
Originally posted by visakh16

UPDATE t
SET t.fnorder=(select count(*) from YourTable WHERE fcpoptext<t.fcpoptext)
FROM YourTable t

Amazing, that worked perfectly. Thanks again Visahk.

welcome David

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources