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

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.

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.

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.

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 ***

cha***@totalise.co.uk wrote:
Show quoteHide quoteA 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.

The PK has an index, so identifying the next row in any direction is simple
and fast:

SELECT MIN(pkcol) FROM table WHERE pkcol > @pkcol --gets the next row
SELECT MAX(pkcol) FROM table WHERE pkcol < @pkcol --gets the previous row

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.


<cha***@totalise.co.uk> wrote in message
Show quoteHide quote

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.

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.

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.

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.

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.

Help with relational set division, please! Good challange!
OPTIMIZER LOCK HINTS
Select most recent date time
What Are The Issues With MS Access Client to SQL Server DB
Column X is same, column Y is different?