Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Location
    South Florida
    Posts
    4

    Unanswered: UPDATE query with ORDER BY clause

    I was hoping that I could write a query with a sub-query in order to perform an UPDATE on the most recent 60,000 records of a table based on a date field, but unfortunately I am receiving an error.

    My query:

    Code:
    SELECT * FROM DMTM
    SET transmit_date = '2012-05-07 00:00:00.000', transmit_status = '1223'
    WHERE temp_pk in
    (
    SELECT TOP 60000 temp_pk, lead_date, transmit_date, transmit_status
    FROM [aroi_live_feeds].[db_owner].[DMTM]
    WHERE lead_date >= '2012-01-08 00:00:00.000'
    AND transmit_status IS NULL
    ORDER BY lead_date desc
    )
    The error received:

    Code:
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near '='.
    Msg 156, Level 15, State 1, Line 10
    Incorrect syntax near the keyword 'ORDER'.
    Thank you in advance for anyone that is able to help me in correcting this.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Odd message, because that is not where your error is.

    Try this:
    Code:
    update	DMTM
    SET	transmit_date = '2012-05-07 00:00:00.000', transmit_status = '1223'
    WHERE temp_pk in
    (
    SELECT TOP 60000 temp_pk
    FROM	[aroi_live_feeds].[db_owner].[DMTM]
    WHERE	lead_date >= '2012-01-08 00:00:00.000'
    	AND transmit_status IS NULL
    ORDER BY lead_date desc
    )
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can't use a SELECT statement to do an UPDATE operation. The subquery in the IN clause can only contain one column. This syntax is close, but not quite what you need. The performance could be greatly improved too.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Feb 2009
    Location
    South Florida
    Posts
    4
    Quote Originally Posted by blindman View Post
    Odd message, because that is not where your error is.

    Try this:
    Tried and received the same error message.

  5. #5
    Join Date
    Apr 2012
    Posts
    213
    I believe that the suggestion of the Blindman should not present the same error message...

    Anyway, try:

    Code:
    with CTE as
    (
        SELECT TOP 60000 transmit_date, transmit_status
        FROM [aroi_live_feeds].[db_owner].[DMTM]
        WHERE lead_date >= '2012-01-08 00:00:00.000'
        AND transmit_status IS NULL
        ORDER BY lead_date desc
    )
    
    UPDATE CTE
    SET transmit_date = '2012-05-07 00:00:00.000', transmit_status = '1223'
    Hope this helps.

  6. #6
    Join Date
    Feb 2009
    Location
    South Florida
    Posts
    4
    imex, your query worked perfectly! I appreciate your help as well as everyone else's help on this.


    Problem solved.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by froubaby View Post
    Tried and received the same error message.
    Then you copied my code incorrectly.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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