Professional Web Applications Themes

Updating TOP X rows ORDERED BY Priority - Microsoft SQL / MS SQL Server

I am trying to update a field to the top X rows in a table when ordered by priority (descending). The Select statement would be SET ROWCOUNT = TopX SELECT * FROM Targets WHERE ID = TID ORDER BY Priority DESC I then want to update a field called TGT_Priority with a constant like 1000 for these rows. I tried doing a SELECT statement in a WHERE clause but it wouldn't allow me to ORDER BY Priority....

  1. #1

    Default Updating TOP X rows ORDERED BY Priority

    I am trying to update a field to the top X rows in a table
    when ordered by priority (descending). The Select
    statement would be

    SET ROWCOUNT = TopX
    SELECT * FROM Targets WHERE ID = TID ORDER BY Priority
    DESC

    I then want to update a field called TGT_Priority with a
    constant like 1000 for these rows. I tried doing a SELECT
    statement in a WHERE clause but it wouldn't allow me to
    ORDER BY Priority.
    Dean Guest

  2. #2

    Default Re: Updating TOP X rows ORDERED BY Priority

    SET ROWCOUNT TopX
    UPDATE Targets SET TGT_Priority = 1000 WHERE primary_key IN
    (SELECT primary_key FROM Targets ORDER BY Priority DESC)


    "Dean" <com> wrote in message
    news:00de01c34aec$d5fcfd10$gbl... 


    Aaron Guest

  3. #3

    Default Re: Updating TOP X rows ORDERED BY Priority

    I don't have access to SQL Server right now, so I cannot
    check it, but try this:

    UPDATE Targets SET TGT_Priority=1000
    WHERE TGT_id IN (SELECT TOP TopX TGT_id FROM Products
    ORDER BY Priority DESC)
    Michal Guest

  4. #4

    Default Re: Updating TOP X rows ORDERED BY Priority

    Can't use a variable in the TOP clause (you would have to use dynamic SQL).



    "Michal Chaniewski" <com.pl> wrote in message
    news:0cb001c34aef$1fcf62f0$gbl... 


    Aaron Guest

  5. #5

    Default Re: Updating TOP X rows ORDERED BY Priority

    Please include DDL and sample data with posts. I've assumed that the PK is
    (id,priority)

    CREATE TABLE Targets (id INTEGER, priority INTEGER, tgt_priority INTEGER NOT
    NULL, PRIMARY KEY (id,priority))

    DECLARE TopX INTEGER, tid INTEGER

    SET TopX = 4
    SET tid = 1

    INSERT INTO Targets VALUES (1,1,0)
    INSERT INTO Targets VALUES (1,2,0)
    INSERT INTO Targets VALUES (1,9,0)
    INSERT INTO Targets VALUES (1,10,0)
    INSERT INTO Targets VALUES (1,11,0)

    UPDATE Targets
    SET tgt_priority = 1000
    WHERE priority <=
    (SELECT MAX(priority)
    FROM
    (SELECT t1.priority
    FROM Targets AS T1
    JOIN Targets AS T2
    ON T1.id = tid AND T2.id = tid
    AND T1.priority>=T2.priority
    GROUP BY T1.priority
    HAVING COUNT(*) <= TopX) X)

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

    "Dean" <com> wrote in message
    news:00de01c34aec$d5fcfd10$gbl... 


    David Guest

  6. #6

    Default Re: Updating TOP X rows ORDERED BY Priority

    What a synchronisation :)
    Michal Guest

  7. #7

    Default Re: Updating TOP X rows ORDERED BY Priority

    Sure, you're right.

    Michal Guest

  8. #8

    Default Re: Updating TOP X rows ORDERED BY Priority

    Sorry. Was supposed to be descending order:

    UPDATE Targets
    SET tgt_priority = 1000
    WHERE priority >=
    (SELECT MIN(priority)
    FROM
    (SELECT T1.priority
    FROM Targets AS T1
    JOIN Targets AS T2
    ON T1.id = tid AND T2.id = tid
    AND T1.priority<=T2.priority
    GROUP BY T1.priority
    HAVING COUNT(*) <= TopX) X)

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

    "David Portas" <org> wrote in message
    news:phx.gbl... 
    NOT 
    >
    >[/ref]


    David Guest

  9. #9

    Default Re: Updating TOP X rows ORDERED BY Priority - THANKS

    Thanks for the replies. I hoped I wouldn't have to write
    dynamic SQL. Oh well.
    Dean
     
    functions, derived tables, 
    message [/ref]
    primary_key IN [/ref]
    DESC) [/ref][/ref]
    table [/ref][/ref]
    Priority [/ref][/ref]
    with a [/ref][/ref]
    SELECT [/ref][/ref]
    to 
    >>
    >>[/ref]
    >
    >
    >.
    >[/ref]
    Dean Guest

  10. #10

    Default Re: Updating TOP X rows ORDERED BY Priority - THANKS

    > Thanks for the replies. I hoped I wouldn't have to write 

    You don't have to. See my earlier reply.

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

    "Dean" <com> wrote in message
    news:021001c34b00$bb25dd90$gbl... 
    > functions, derived tables, 
    > message [/ref]
    > primary_key IN [/ref]
    > DESC) [/ref]
    > table [/ref]
    > Priority [/ref]
    > with a [/ref]
    > SELECT [/ref]
    > to 
    > >
    > >
    > >.
    > >[/ref][/ref]


    David Guest

  11. #11

    Default Re: Updating TOP X rows ORDERED BY Priority

    Ok, so using the same methodology that allows you to use order by in a view,
    you could try (again, untested):


    SET ROWCOUNT TopX

    UPDATE Targets SET TGT_Priority = 1000 WHERE primary_key IN
    (
    SELECT TOP 100 PERCENT primary_key FROM Targets
    ORDER BY Priority DESC
    )




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


    Aaron Guest

  12. #12

    Default Re: Updating TOP X rows ORDERED BY Priority

    Doesn't help. SET ROWCOUNT only applies to the final result set or in this
    case to the result of the UPDATE, not to a subquery. Therefore:

    UPDATE Targets SET TGT_Priority = 1000 WHERE primary_key IN
    (
    SELECT TOP 100 PERCENT primary_key FROM Targets
    ORDER BY Priority DESC
    )

    is equivalent to

    UPDATE Targets SET TGT_Priority = 1000

    ROWCOUNT does restrict how many rows get updated but your code just updates
    some arbitrary rows.

    Here's a repro:

    CREATE TABLE Targets (primary_key INTEGER, priority INTEGER, tgt_priority
    INTEGER NOT NULL, PRIMARY KEY (primary_key,priority))

    DECLARE TopX INTEGER
    SET TopX = 1

    INSERT INTO Targets VALUES (1,1,0)
    INSERT INTO Targets VALUES (2,2,0)
    INSERT INTO Targets VALUES (3,3,0)

    SET ROWCOUNT TopX

    UPDATE Targets SET TGT_Priority = 1000 WHERE primary_key IN
    (
    SELECT TOP 100 PERCENT primary_key FROM Targets
    ORDER BY Priority DESC
    )

    Result:

    primary_key priority tgt_priority
    ----------- ----------- ------------
    1 1 1000
    2 2 0
    3 3 0

    Priority=1 gets updated, not priority=3 as intended by "ORDER BY Priority
    DESC"

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



    David Guest

Similar Threads

  1. Replies: 3
    Last Post: June 6th, 10:56 AM
  2. Updating Multiple Database Rows Simultaneously
    By scott in forum PHP Development
    Replies: 5
    Last Post: October 12th, 02:27 PM
  3. Replies: 0
    Last Post: September 16th, 04:37 PM
  4. Updating number of rows in tables
    By Scott Brown in forum PHP Development
    Replies: 1
    Last Post: September 14th, 05:05 AM
  5. Replies: 0
    Last Post: September 10th, 05:33 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