In SQL Server 2005, you can use the following command: UPDATE TOP 1...

SQL Server 2000 does not have this feature.  Is there a way around
this?  I tried using a WHERE EXISTS clause, but that didn't work.

If you give better specifications, you will probably get a better answer.  I
don't know what "tried" or "didn't work" mean and I ave no idea how to
correct your UPDATE statement to achieve the desired results.  One thought
is:

SET ROWCOUNT 1;
UPDATE ...
SET ROWCOUNT 0;

However, if the above UPDATE statement affects more than one row, there
won't be a straightforward way to control which of those rows is going to be
affected by the UPDATE statement.  Is there any reason you can't identify
the exact row you want to modify?





<rzale***@gmail.com> wrote in message

Show quote

Hide quote

On Wed, 13 Sep 2006 17:32:34 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@dnartreb.noraa> wrote:

Another "gotcha" on this one is that if a trigger fires because of the
update any actions in the trigger are subject to the ROWCOUNT
limitation too.

Roy Harvey
Beacon Falls, CT

Come on Roy, nobody uses triggers.

Just kidding!

Show quote

Hide quote

The 'set rowcount 1' statement will work in my case.

Thanks

Aaron Bertrand [SQL Server MVP] wrote:

Show quote

Hide quote

rzale***@gmail.com wrote:
SET ROWCOUNT 1 will in fact achieve the same as UPDATE TOP 1 - namely
it updates some RANDOM row out of those referred to by the UPDATE
statement. But if multiple rows in your table are all equally valid for
this update then you should ask yourself whether a) your table has
redundant data OR b) your specification is wrong/incomplete.

On Wed, 13 Sep 2006 17:39:19 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@dnartreb.noraa> wrote:

Except to send emails, of course.  Reading these groups that appears
to be a popular use for triggers.

That particular gotcha was a more common issue back in the
bad-old-days when we had triggers but didn't have DRI or TOP, so a
trigger implementing cascading deletes the hard way was pretty
vulnerable.

Roy

InSQL Server 2005 you can do

WITH someCTE AS
(
  SELECT *,
    ROW_NUMBER() OVER(ORDER BY col1 DESC) AS RowNum
  FROM dbo.Tables
)
UPDATE someCTE
  SET coltobeupdated = ......
WHERE RowNum <= 3;





<rzale***@gmail.com> wrote in message

Show quote

Hide quote

Little bug in sql2005?
Need Consecutive Query
**SORT THE PHYSICAL ORDER ON A TABLE'S COLUMNS**
INSTEAD OF TRIGGERS DO NOT SUPPORT DIRECT RECURSION
problem with synonyms