Professional Web Applications Themes

Script question - Microsoft SQL / MS SQL Server

I can't figure out how to create a script to select top 50 * from table update a column with a value then select the next 50, update the same column with different data select the next 50 and so on any ideas? Thanks, Aaron...

  1. #1

    Default Script question

    I can't figure out how to create a script to

    select top 50 *
    from table

    update a column with a value

    then select the next 50, update the same column with different data

    select the next 50 and so on

    any ideas?

    Thanks,
    Aaron
    Aaron Guest

  2. #2

    Default Re: Script question

    You have to use ORDER BY with TOP to get a meaningful result. Tables are
    unordered sets of rows. What column(s) defines the sequence in your table?
    Here's an example to UPDATE the TOP 50 rows based on a column called
    "Keycol":

    UPDATE Sometable
    SET col1 = 123
    WHERE keycol IN
    (SELECT TOP 50 keycol
    FROM Sometable
    ORDER BY keycol)

    However, if you want to update different values based on a sequence column I
    would do this:

    UPDATE Sometable
    SET col1 =
    CASE
    WHEN keycol BETWEEN 1 AND 50 THEN 123
    WHEN keycol BETWEEN 51 AND 100 THEN 456
    WHEN keycol BETWEEN 101 AND 150 THEN 789

    .... etc

    END

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  3. #3

    Default Re: Script question

    You need to have some column in the table to define what you mean by "TOP
    30". A fundamental principle of a relational database is that tables are
    unordered sets of rows. When you query a table, the rows are returned in
    some arbitrary order unless you specify ORDER BY. Some rows may well be in
    the order in which they were inserted but you certainly can't guarantee
    that, especially in a query where indexing may be used to optimise the order
    in which rows are fetched. This applies even if you open the table for
    editing in Enterprise Manager or Query yzer.

    If you don't have such a column you could add one as an IDENTITY column
    (although you can't control the sequence in which the values are assigned to
    the rows). Or better, just add an INTEGER column and populate it manually or
    in your application.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  4. #4

    Default Re: Script question

    Actually, in this case, I don't mind which records are updated, as long
    as they are 30 records with a null value for round. We kind of want them
    to be random...


    Does this change anything?

    If it doesn't, I do have an ID column with unique values.

    Aaron

    "David Portas" <org> wrote in
    news:phx.gbl:
     

    Aaron Guest

Similar Threads

  1. question con d'action script
    By Isa in forum Macromedia Flash Actionscript
    Replies: 3
    Last Post: February 17th, 11:29 AM
  2. script question for BG change
    By gregkr webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: February 6th, 11:39 AM
  3. strange script question
    By Scottie Armani in forum FileMaker
    Replies: 5
    Last Post: July 31st, 08:52 PM
  4. ASP -> PERL Script QUestion..PLEASE
    By Tassilo v. Parseval in forum PERL Modules
    Replies: 0
    Last Post: June 28th, 05:26 AM
  5. question on a unix script
    By Fletcher Glenn in forum UNIX Programming
    Replies: 0
    Last Post: June 26th, 09:31 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139