identify 'next' pk value  
Author Message
Prasadkakarla





PostPosted: Sat Jan 07 04:25:29 CST 2006 Top

SQL Server Developer >> identify 'next' pk value

Hi,

I want to 'move' a row up or down in a table (pk not carried with it).
Strikes me that the easiest way to accomplish this is to copy the
values for that row and the row above or below into variables then do
updates on those two rows to copy the values over. Fine except I don't
see any way to identidy the 'next' row in any direction as T-SQL has no
concept of the row 'position'?

How can I identify the 'next' row?

TIA,

Chandy

SQL Server3  
 
 
Amish





PostPosted: Sat Jan 07 04:25:29 CST 2006 Top

SQL Server Developer >> identify 'next' pk value Why you want to move records up and down in table?

Data is guaranteed in order only on the column on which you have
created clustered index. If you do not have clustered index sql server
does not store data in particular order. If you want to use data in some
order you have to user order by in your query.


If you want to change data of some row with related some row then you
have some choice is to create a trigger , which will update data of row
in a table with respect to inserted row at time of insert or update. or
you can format data run time using SP or function if possible.

Please post ddl and data.

Regards
Amish

*** Sent via Developersdex http://www.developersdex.com ***
 
 
Brian





PostPosted: Sat Jan 07 07:39:31 CST 2006 Top

SQL Server Developer >> identify 'next' pk value The PK has an index, so identifying the next row in any direction is simple
and fast:




I question the logic of 'moving' rows, however. I would need more
information to make a firm judgement, but at first glance, the only reason I
can think of would be if you're depending on the order of IDENTITY values.
Surrogates should not add anything to the rows that they represent. This
means that neither the magnitude nor the relative position within a table
should be relied upon. You should be able to replace an IDENTITY column
with a uniqueidentifier DEFAULT(NEWID()) column (and an update trigger that
prevents changes to it) without breaking anything (except maybe
performance). If you cannot, then you're using IDENTITY incorrectly. If
the sequencing isn't controlled by IDENTITY, then there's no need to 'move'
rows: simply update the sequencing column instead.




> Hi,
>
> I want to 'move' a row up or down in a table (pk not carried with it).
> Strikes me that the easiest way to accomplish this is to copy the
> values for that row and the row above or below into variables then do
> updates on those two rows to copy the values over. Fine except I don't
> see any way to identidy the 'next' row in any direction as T-SQL has no
> concept of the row 'position'?
>
> How can I identify the 'next' row?
>
> TIA,
>
> Chandy
>


 
 
Jens





PostPosted: Sat Jan 07 02:39:39 CST 2006 Top

SQL Server Developer >> identify 'next' pk value There is no "next" row as there is no first or second row unless you
use the ORDER clause. Perhaps it would be better to provide some DDL
and sample data which would clarify your designed enviroment.

http://www.aspfaq.com/5006

HTH, Jens Suessmeyer.

 
 
Jens





PostPosted: Sat Jan 07 02:49:54 CST 2006 Top

SQL Server Developer >> identify 'next' pk value Sorry, perhaps this post will appear twice but the google newsreader
which I use is unreliable, so I do have to write the post twice:

There is no last, first next or previous row in the table unless you
use a ORDER clause. So perhpas it would be better to send some DDL and
/or sample data tp help you further.

HTH, Jens Suessmeyer.

 
 
Jens





PostPosted: Sat Jan 07 02:51:22 CST 2006 Top

SQL Server Developer >> identify 'next' pk value Sorry, perhaps this post will appear twice but the google newsreader
which I use is unreliable, so I do have to write the post twice:

There is no last, first next or previous row in the table unless you
use a ORDER clause. So perhpas it would be better to send some DDL and
/or sample data tp help you further.

HTH, Jens Suessmeyer.

 
 
David





PostPosted: Sat Jan 07 04:47:14 CST 2006 Top

SQL Server Developer >> identify 'next' pk value
> Hi,
>
> I want to 'move' a row up or down in a table (pk not carried with it).
> Strikes me that the easiest way to accomplish this is to copy the
> values for that row and the row above or below into variables then do
> updates on those two rows to copy the values over. Fine except I don't
> see any way to identidy the 'next' row in any direction as T-SQL has no
> concept of the row 'position'?
>
> How can I identify the 'next' row?
>
> TIA,
>
> Chandy

A table is an unordered set. If you want to order a set of data you
need to add a column to determine that sequence and then query the
table using "ORDER BY sequence_column". IDENTITY would be a bad choice
for the sequence column because you cannot update it and because
IDENTITY isn't supposed to be exposed to business users at any time.

Add a sequence number column to your table and update that rather than
try to copy a row.

--
David Portas
SQL Server MVP
--

 
 
--CELKO--





PostPosted: Sun Jan 08 18:56:07 CST 2006 Top

SQL Server Developer >> identify 'next' pk value >> I want to 'move' a row up or down in a table (pk not carried with it). <<

Then you have no PRIMARY KEY at all! By definition, a key is a subset
of attributes of an entity. You do not "carry it" or "move it" -- it
is inseparable from the entity; change it and the entity changes
completely.

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to havs a column
that defines that ordering.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

What you have now is meaningless. You need to read at least one book on
RDBMS.

 
 
--CELKO--





PostPosted: Sun Jan 08 20:34:54 CST 2006 Top

SQL Server Developer >> identify 'next' pk value >> want to 'move' a row up or down in a table <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

What you are talkign about is a sequential file, like yoiu would have
in a magnetic tape file sytem in the 1950's.

If you want an ordering, then you need to have a column that defines
that ordering. This is called the Information Principle and it is one
of Codd's rules which you will learn when you fianlly read an intro
book. But since you did not post any DDL, we have no idea what that
column might be.

 
 
--CELKO--





PostPosted: Sun Jan 08 21:39:42 CST 2006 Top

SQL Server Developer >> identify 'next' pk value >> want to 'move' a row up or down in a table <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

What you are talking about is a sequential file, like you would have in
a magnetic tape file sytem in the 1950's.

If you want an ordering, then you need to have a column that defines
that ordering. This is called the Information Principle and it is one
of Codd's rules which you will learn when you fianlly read an intro
book. But since you did not post any DDL, we have no idea what that
column might be.

 
 
--CELKO--





PostPosted: Sun Jan 08 22:18:24 CST 2006 Top

SQL Server Developer >> identify 'next' pk value >> want to 'move' a row up or down in a table <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

What you are talkign about is a sequential file, like yoiu would have
in a magnetic tape file sytem in the 1950's.

If you want an ordering, then you need to have a column that defines
that ordering. This is called the Information Principle and it is one
of Codd's rules which you will learn when you fianlly read an intro
book. But since you did not post any DDL, we have no idea what that
column might be.

 
 
--CELKO--





PostPosted: Sun Jan 08 21:08:55 CST 2006 Top

SQL Server Developer >> identify 'next' pk value >> want to 'move' a row up or down in a table <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

What you are talkign about is a sequential file, like yoiu would have
in a magnetic tape file sytem in the 1950's.

If you want an ordering, then you need to have a column that defines
that ordering. This is called the Information Principle and it is one
of Codd's rules which you will learn when you fianlly read an intro
book. But since you did not post any DDL, we have no idea what that
column might be.