SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    update statement and TOP n

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

  2. #2
    SitePoint Addict Guimauve's Avatar
    Join Date
    Aug 2004
    Location
    Chicago
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The query that you are assigning to the variable must return a single value. Are you looking for the sum of all of the quantities?

  3. #3
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No,
    if i was looking for the sum of the quantities, i would use sum.

    I am trying to get a single value for the @quantities variable.
    to that my query would run as
    FROM (SELECT TOP 100 FROM authors ORDER BY pins) AS pin_numbers
    where @quantities could be any integer selected from my database

    rgds

  4. #4
    SitePoint Addict Guimauve's Avatar
    Join Date
    Aug 2004
    Location
    Chicago
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you saying that you want to select the TOP 100 rows from a table and you want to control how many exactly by assigning the number of rows that you want into an @ variable? If that is the case you should know that the number after top cannot be a variable.

    If you want to do that you would have to create a dynamic SQL statement and then execute it. For Example:

    Code:
    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
    This is just an example of course

    Charles

  5. #5
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent yet so simple

    Thanks a lot Charles
    rgds
    Afrika


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •