Update and TOP n keyword - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update and TOP n keyword

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2004-12-20 : 14:03:04

hi,
i am tyring to work with the TOP keyword and assign a variable to it, but it keeps giving me an error.

here is my code

[CODE]
declare @updated int, @quantity int
select @updated = ( select transactionID from transaction_table where userid = 'afrika'

select @quantity = (select quantity_1 from items_purchased where userid = 'afrika'

begin
UPDATE G_plan
SET state = @updated
FROM (SELECT TOP @quantity FROM authors ORDER BY pins) AS pin_numbers
WHERE G_plan.pins = pin_numbers.pins[/CODE]
end

However i get an error, when i try to use @quantity instead of TOP 100

I am trying to assign a dynamic number to the quantity to be updated

how could i achieve this
rgds
Afrika

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-20 : 14:16:32

using dynamic sql or using
set rowcount @quantity

Go with the flow & have fun! Else fight the flow

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2004-12-20 : 14:22:56

i read an article in the help manual F1 key. That rowount should not be used with the update statement.

Thats why i left it out

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2004-12-20 : 14:33:03

i read an article in the help manual F1 key. That rowount should not be used with the update statement.

Thats why i left it out

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-20 : 14:34:53

well then dynamic is the way to go i guess....

Go with the flow & have fun! Else fight the flow

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2004-12-20 : 14:44:26

i read an article in the help manual F1 key. That rowount should not be used with the update statement.

Thats why i left it out

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-20 : 14:46:51

Afrika, please post the statement that you are referring to as I am unable to find it in SQL Server Books Online. The only thing that I can find is this:

quote:

The setting of the SET ROWCOUNT option is ignored for UPDATE statements against remote tables and local and remote partitioned views.

The above does not apply to your query.

Tara

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2004-12-20 : 14:50:39

hi tduggan,
press the F1 KEY and type set rowcount. You get the info below
rgds
Afrika

It is recommended that DELETE, INSERT, and UPDATE statements currently using SET ROWCOUNT be rewritten to use the TOP syntax. For more information, see DELETE, INSERT, or UPDATE.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-20 : 14:54:41

In order to do this with dynamic sql, you'll have to build the variables and run the query all in one exec statement as the variables are out of scope when you run the query with dynamic sql.

Tara

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2004-12-20 : 14:58:33

i built the variables in the 1st statement above.

so how do i run it ?
declare @quantity int
select @quantity = (select....)
exec ... ?

please advice

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-20 : 15:08:34

The point is that you can't do it like that. Check out the dynamic SQL article:

http://www.sqlteam.com/item.asp?ItemID=4599

Tara

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2004-12-21 : 06:03:57

hi
i got it solved, and posting it here just in case anyone else needs it.
rgds
Afrika

DECLARE @Limit INTEGER
DECLARE @SQL VARCHAR(2000)
SET @Limit = 100 -- For example, make this a query assignment

SET @SQL = 'SELECT TOP ' + @Limit + ' * FROM authors ORDER BY pins'
EXEC @SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources