How to write 'update' statement to update top "n" records using Sql server 2000?

How to write 'update' statement to update top "n" records using Sql server 2000?

Post by ABC » Fri, 05 May 2006 19:44:53


How to write 'update' statement to update top "n" records using Sql server
2000? "n" is unknown and inputted by user.
 
 
 

How to write 'update' statement to update top "n" records using Sql server 2000?

Post by Will » Fri, 05 May 2006 19:57:04

as far as I can tell there are 2 separate questions here:

how to update top n.


how to have n passed in by the user.


to update top N, I would update tblA SET col = @val WHERE IDCol in
(SELECT TOP 100 IDCOl FROM tblA)

if you don't have any kind of key on the table you're in trouble.

to dynamically assign N I would use dynamic sql

so for example :

CREATE TABLE #tmp(IDCol int IDENTITY(1,1), colA int)

INSERT INTO #tmp(ColA)
SELECT 2

INSERT INTO #tmp(ColA)
SELECT ColA * IDCol
FROM #tmp
INSERT INTO #tmp(ColA)
SELECT ColA * IDCol
FROM #tmp
INSERT INTO #tmp(ColA)
SELECT ColA * IDCol
FROM #tmp
INSERT INTO #tmp(ColA)
SELECT ColA * IDCol
FROM #tmp

SELECT * FROM #tmp
DECLARE @N int --actually have this as a parameter to your stored
procedure
SET @N = 10
DECLARE @Val nvarchar(10)
SET @Val = 1
DECLARE @SQL nvarchar(4000)
SET @SQL = 'UPDATE #tmp SET colA = ' + @Val + ' WHERE IDCol IN (SELECT
TOP ' + CAST(@N as nvarchar(10)) + ' IDCol FROM #tmp)'

exec sp_executesql @SQL
select * FROM #tmp

drop table #tmp


Cheers
Will

 
 
 

How to write 'update' statement to update top "n" records using Sql server 2000?

Post by kriskir » Fri, 05 May 2006 20:07:00

For dynamic N why not just use SET ROWCOUNT?

i.e.

SET ROWCOUNT @n
SELECT IDCol INTO #temp1 FROM foo WHERE ......
SET ROWCOUNT 0

UPDATE foo
SET bar = @Val
WHERE IDCol IN (SELECT IDCol FROM #temp1)

DROP TABLE #temp1
 
 
 

How to write 'update' statement to update top "n" records using Sql server 2000?

Post by David Port » Fri, 05 May 2006 20:07:19


For example, TOP 3 based on the key, col1:

CREATE TABLE tbl (col1 INT PRIMARY KEY, col2 INT NOT NULL);

INSERT INTO tbl (col1, col2)
SELECT 1,0 UNION ALL
SELECT 2,0 UNION ALL
SELECT 3,0 UNION ALL
SELECT 4,0 UNION ALL
SELECT 5,0 ;

DECLARE @n INT;
SET @n = 3;

UPDATE tbl
SET col2 = 1
WHERE @n >
(SELECT COUNT(*)
FROM tbl AS T
WHERE T.col1 < tbl.col1);

It is also worth noting that the UPDATE TOP feature in SQL Server 2005
does NOT solve the same problem. UPDATE TOP (n) will update a RANDOM
selection of n rows so it isn't suitable for performing updates in any
predetermined order. 2005's RANK() function does help however:

WITH t AS
(SELECT RANK() OVER (ORDER BY col1) AS rnk,
col1, col2
FROM tbl)
UPDATE t
SET col2 = 2
WHERE rnk <= @n;

Hope this helps.

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://www.yqcomputer.com/ (en-US,SQL.90).aspx
 
 
 

How to write 'update' statement to update top "n" records using Sql server 2000?

Post by David Port » Fri, 05 May 2006 20:13:11


At least two good reasons not to do it this way. Firstly, you cannot
determine which n rows will be updated. Usually "Top N" means the rows
are to be ranked by some criteria even though that criteria was
unspecified in this case. Will's example is also non-deterministic
because he didn't specify ORDER BY with TOP.

The second reason to avoid ROWCOUNT is that Microsoft say (in the 2005
BOL) that it may be dropped in a future version. Although the OP is
still using 2000 it might be better to reduce reliance on a deprecated
feature where feasible.

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://www.yqcomputer.com/ (en-US,SQL.90).aspx
 
 
 

How to write 'update' statement to update top "n" records using Sql server 2000?

Post by kriskir » Fri, 05 May 2006 20:24:20


<snip>

What's if 'TOP n' is not based on a unique value? For example, if we
have these rows:

ID Value
1 20
2 10
3 20
4 10
5 5

and we want to update the top 3 values, and in the case of a tie, then
any row will do as long as only 3 rows are updated in total (in this
case ID's 1, 3 and (2 or 4) should be updated).

Kris
 
 
 

How to write 'update' statement to update top "n" records using Sql server 2000?

Post by kriskir » Fri, 05 May 2006 20:42:34


Are you sure SET ROWCOUNT won't work with an ORDER BY? I found this
comment on another web site (don't have access to BOL currently so
can't confirm):

"set rowcount has always worked the same as top.
At one time there was a mistake in bol saying that it was applied
before the order by clause but that has been corrected now."

Kris
 
 
 

How to write 'update' statement to update top "n" records using Sql server 2000?

Post by David Port » Sat, 06 May 2006 00:16:44


SET ROWCOUNT will work as expected with ORDER BY in a SELECT statement.
However, the ORDER BY will not necessarily affect the result of a
SELECT INTO statement because inserts are not logically ordered. The
same goes for an explicit INSERT statement. It might work some of the
time or even most of the time but Books Online doesn't document any
such behaviour and there are certainly cases where INSERT fails to
respect the ORDER BY clause on a SELECT statement. Don't rely on
ordered inserts is my advice.

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://www.yqcomputer.com/ (en-US,SQL.90).aspx
 
 
 

How to write 'update' statement to update top "n" records using Sql server 2000?

Post by David Port » Sat, 06 May 2006 00:23:21


You can extend my UPDATE to incorporate whatever key column(s) you
have:

UPDATE tbl
SET col2 = 3
WHERE @n >
(SELECT COUNT(*)
FROM tbl AS T
WHERE T.col1 < tbl.col1
OR (T.col1 = tbl.col1
AND T.key_col < tbl.key_col)) ;

Including the key hopefully makes more sense than "any row will do".
Admittedly this just won't perform for large updates. In those cases
you may have to use a dynamic TOP query. This problem is solved in 2005
but TOP is still a mess in SQL Server 2000.

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://www.yqcomputer.com/ (en-US,SQL.90).aspx