Post reply

Update Variable Number of Rows

  • April 24, 2008 at 1:41 pm

    #71935

    Hi,

    I would like to create a stored procedure that would take an integer input @NumRows and update the corresponding number of rows in a table. Something like:

    UPDATE table_A

    SET column_1 = 'Some Value'

    WHERE columnID IN (SELECT TOP @NumRows columnID FROM table_A ORDER BY NEWID());

    How can this be done?

    Thanks,

    ywb

  • April 24, 2008 at 1:52 pm

    #806873

    SET ROWCOUNT @num

    update

    set rowcount 0

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • April 24, 2008 at 1:59 pm

    #806885

    I'm sorry, I don't get it...

  • April 25, 2008 at 7:33 am

    #807167

    What Steve is saying is the the 'SET ROWCOUNT' command while achieve what you want to do.

    Here is an example using your situation:

    DECLARE @NumRecs int, @Value varchar(25)

    SET @NumRecs = 100

    SET @Value = 'SomeValue'

    SET ROWCOUNT @NumRecs

    UPDATE {table}

    SET {column} = @Value

    WHERE ID IN (SELECT TOP 100 PERCENT ID FROM {table} ORDER BY NEWID())

    SET ROWCOUNT 0

    The 'SET ROWCOUNT' command tells SQL Server to only process the stated number of records. In this case 100 records

    The 'SET ROWCOUNT 0' tells SQL Server to process all records. When ever you use SET ROWCOUNT #, it is good practice to always issue the SET ROWCOUNT 0 after you are done. If not, you will be stuck with the limitation of onle the # number of rows processed in each SQL statement you execute.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply