Update existing table alphabetically

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 existing table alphabetically

Author

Topic

starydynamo
Starting Member

12 Posts

Posted - 2011-05-24 : 07:23:44

Hi all,

I am looking to update an existing table (TESTMODULE) alphabetically by strName everytime a software is loaded.

My SQL code is as follows:


UPDATE TESTMODULE
(SELECT *
FROM TESTMODULE
ORDER BY strName ASC)

Thanks in advance

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-24 : 07:26:06

What do you want to update to?
And where does the alphabetical bit come in?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

starydynamo
Starting Member

12 Posts

Posted - 2011-05-24 : 07:34:31

I just want to update an existing table (TESTMODULE) alphabetically using the strName (list of surnames).

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-24 : 07:41:44

Please give example table structure, example data and wanted result in relation to example data.

No, you're never too old to Yak'n'Roll if you're too young to die.

starydynamo
Starting Member

12 Posts

Posted - 2011-05-24 : 07:50:09

Current table

ID strName strFirstName StrOccupation
1 Walsh Robert Milkman
2 Morris Ben Plumber
3 Beckham David Plumber
4 Smith Jim Electician

Desired order table (strName alphabetically sorted)

ID strName strFirstName StrOccupation
3 Beckham David Plumber
2 Morris Ben Plumber
4 Smith Jim Electician
1 Walsh Robert Milkman

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-24 : 08:00:23

update table
set strname = t2.strname ,
strFirstName = t2.strFirstName ,
StrOccupation = t2.StrOccupation
from table t1
join (select ID, seq = row_number() over (order by ID) from table) t1
on t1.ID = t2.ID
join (select strname, strFirstName, StrOccupation,
seq = row_number() over (order by strname) from table) t1
on t1.seq = t2.seq

The extra derived table is to allow for gaps in the ID.

Why would you want to do this?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-24 : 08:05:52

So you want to get a table with rows in a specific order.
That's not a reliable possibility.
Get your wanted order when doing the SELECT with ORDER BY please.
Don't bother about the order of data in a table.

No, you're never too old to Yak'n'Roll if you're too young to die.

starydynamo
Starting Member

12 Posts

Posted - 2011-05-24 : 08:11:10

I want to order the table as I want to generate a PDF so I require the data to be alphabetically sorted at all times. The problem is that as I am always entering new data in the table, I need something automatic setup so everytime I print the pdf the table sorts itself (was going to call a SQLquery).

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-24 : 08:24:26

Somewhere in the process that takes the data from the table and sends to the PDF file, there has to be a "select" statement, which retrieves the data from the table. select statements allow you to specify an order by clause - if you add the order by clause in that select statement, you will never have to worry about it again, even if you add or remove rows.

There is no other reliable way in SQL server to get the data from a table and be guaranteed that it is sorted.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-24 : 08:25:40

Why not use a view to generate the pdf - use an order by in the view.
Even if you update the rows with the code I gave it doesn't mean they will be returned in that order if you don't include an order by.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

starydynamo
Starting Member

12 Posts

Posted - 2011-05-24 : 08:34:19

Just tested the code with an alphabetically sorted table and it doesn't generate the strName alphabetically. darn it

I can't believe this is so difficult?!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-24 : 09:27:58

When you said "alphabetically sorted table", what did you mean?

I am repeating/rephrasing what Fred and Nigel had said earlier,but:

SQL Server does not have the concept of a sorted table. It thinks of a table as a pile of rows dumped in its storage in whatsoever order it pleases. And when you do a select from that table, it claims it has the right to retrieve it anyway it pleases and return to you in any order that is convenient for its purposes UNLESS you specify an order by clause in your select statement.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-24 : 10:13:41

It's not difficult - I just think you haven't identified what you need to get the output you want.
Once you can define the requirements I'm sure it will be easy.
Pretty sure that an attempt to order the data in the table won't help - and even if it did it would be due to the qay queries were executed by the server which could change at any time.

The code I gave has a few issues - here's the correct version

declare @t table (ID int, strName varchar(20), strFirstName varchar(20), StrOccupation varchar(20))

insert @t select 1, 'Walsh', 'Robert', 'Milkman'
insert @t select 2, 'Morris', 'Ben', 'Plumber'
insert @t select 3, 'Beckham', 'David', 'Plumber'
insert @t select 4, 'Smith', 'Jim', 'Electician'

update @t
set strname = t3.strname ,
strFirstName = t3.strFirstName ,
StrOccupation = t3.StrOccupation
from @t t1
join (select ID, seq = row_number() over (order by ID) from @t) t2
on t1.ID = t2.ID
join (select strname, strFirstName, StrOccupation,
seq = row_number() over (order by strname) from @t) t3
on t2.seq = t3.seq

select * from @t

ID strName strFirstName StrOccupation
----------- -------------------- -------------------- --------------------
1 Beckham David Plumber
2 Morris Ben Plumber
3 Smith Jim Electician
4 Walsh Robert Milkman

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

starydynamo
Starting Member

12 Posts

Posted - 2011-05-24 : 11:52:37

'It's not difficult - I just think you haven't identified what you need to get the output you want.
Once you can define the requirements I'm sure it will be easy.'

I am not sure how I should specify my output??? I thought I did this in the earlier post? I basically am using ASP modules and calling in parts the database where needed. So the end user would use a browser based software to view different parts of the database when required.

If SQL doesn't care about alphabetically ordered table won't using a query with your code have no affect either??

Really not sure what to do now!! Should I use ASP to order the data instead?

Thanks so much for all your help!!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-24 : 12:22:43

A Set (table) by definition has no order. As already mentioned (several times) SQL Server does not guarantee order unless you specify an ORDER BY clause on your SELECT.

For exmaple:

SELECT *
FROM MyTable
ORDER BY strName

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-24 : 13:21:11

>> If SQL doesn't care about alphabetically ordered table won't using a query with your code have no affect either??
See my earlier comments about the order by clause.

The problem is that you are trying to state you problem in terms of the order in which the datat is stored in the table. That will have no effect on your output - and in fact you will never know how it is stored (probably), and that has nothing to do with the order of access anyway.

It now sounds like you just want the data extracted by an asp application to be ordered - that's a cursor in which ordering is meaningful. It should just be matter of including an order by statement in the extract query.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

starydynamo
Starting Member

12 Posts

Posted - 2011-05-29 : 10:03:34

Yeah I realize that now. I have to call the query/SQL qithin ASP in order to alphabetically sort it. (although am a little clueless how to do this?

Here is my current code: (causes an http500 error)

sqlorderby="SELECT * FROM TESTMODULE ORDER BY StrName"
Set rs=conn.Execute(sqlorderby)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources