Is there a way to sort the table after records are inserted?

Question

Is there a way to sort the table after records are inserted?

Sanz

Hall of Fame

Points: 3412
More actions
August 28, 2013 at 4:33 am

#276616

Is there a way to sort the table after records are inserted?
Declare @tab Table(name varchar(10), value varchar(10))
Declare @mystring varchar(10)
set @mystring='AA'
insert into @tab
select 'A', 1
union
select 'AA',2
select @mystring=REPLACE(@mystring,name,value) from @tab order by LEN(name) desc
select @mystring
Here is my problem
1. I create a table with two cols Name and Value
2. I insert records into this table using a union statement. (Ex records : {A,1},{AA,2})
3. Now I have a string which I need to be replaced.
Ex: if my string is AA, I need it to be replaced by 2. But now what happen is since in the table A is before AA, it replaces it with two 1’s , ie 11
Sanz

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

The problem is you can't assign multiple values to a single variable. So your "select @mystring=...." statement will only return the last updated value. You also has the order of the parameters in the REPLACE statement wrong. The first parameter is the original value, the second parameter is the string to be replaced.

And the most important rule: an order is only guaranteed if an ORDER BY is used.

Use this code instead:

Declare @tab Table(name varchar(10), value varchar(10))

Declare @mystring varchar(10)

set @mystring='AA'

insert into @tab

select 'A', 1

union

select 'AA',2

select

name

, value

, REPLACE(name,@mystring,value) as new_value

from @tab

order by name

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Sanz Hall of Fame Points: 3412 More actions · Answer 2

I have used order by Len(name) desc so that AA will come first, but since order by will apply atlast, it will not work.

One option I have in mind is to use a subquery like

Declare @tab Table(name varchar(10), value varchar(10))

Declare @mystring varchar(10)

set @mystring='AA'

insert into @tab

select * from (select 'A' Name , 1 Value

union

select 'AA',2) A order by LEN(name) desc

select @mystring=REPLACE(@mystring,name,value) from @tab

select @mystring

What you think?

Sanz

HanShi SSC-Dedicated Points: 33507 More actions · Answer 3

Because you assign the updated values from a table to a single variable, it will still be possible to get the wrong results. The update on the table could result in multiple rows. Only the last updated row will be visible in the variable.

So the basic of your solution is not correct. Do not assign multiple rows to a single variable. If you really need to assign an updated value to the variable, at least use the TOP 1 clause in combination with an ORDER BY. This will guarantee the row to be returned. Or apply a WHERE clause that will return a single row.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Sanz Hall of Fame Points: 3412 More actions · Answer 4

The same query will work with two rows. The actual scenario has 100’s of data.

Sanz

HanShi SSC-Dedicated Points: 33507 More actions · Answer 5

Sanz (8/28/2013)
The same query will work with two rows. The actual scenario has 100’s of data.

And like I said before: only ONE value of this multi-row query wil be stored in the @mystring variable.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Couple steps back...

What are you trying to do?

Bear in mind that a table has no defined order (by definition). A resultset only has a guaranteed order if an ORDER BY statement is specified on the query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Thomas.Carr 15137 SSC Rookie Points: 48 More actions · Answer 7

select @mystring=REPLACE(@mystring,name,value) from @tab

We don't sort tables. The entire relational world is based on the concept that absolutely no assumptions can ever be made on the physical order of rows in a table. Among other advantages, this frees RDBMS systems to place physical rows however it needs them. So "sort a table" is meaningless. We only sort result sets.

If you want an action to apply to one particular row, you must clearly identify that row. In this simple case, you can change your statement to:

select @mystring=REPLACE(@mystring,name,value) from @tab where name = @mystring

But your statement is more complex than it needs to be. Why do you even need the REPLACE?

select @mystring=value from @tab where name = @mystring

TommCatt
In theory, there is no difference between theory and practice. In practice, there is.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 8

You should be able to use a correlated subquery, like so:

SELECT

@mystring,

REPLACE(@mystring, name, value) AS newstring

FROM (

SELECT TOP (1) * FROM @tab WHERE @mystring LIKE '%' + name + '%' ORDER BY LEN(name) DESC

) AS correlated

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".