Query is updating all records, not one

  • I have a question on a query...Im trying to update a value from a table, but it should only update top 1. Here's my query:

    UPDATE T_VM_QUEUE SET STATUS_CD = 'P' WHERE STATUS_CD = (SELECT TOP (1) STATUS_CD FROM T_VM_QUEUE WHERE Dialer_Tbl_Nme = '5010_5010' ORDER BY ImportRuleDate ASC)

    Instead of updating Top 1, it updates all the records. What the heck am I doing wrong?

  • figured it out from stackoverflow

    WITH CTE AS (SELECT TOP (1) * FROM T_VM_QUEUE WHERE STATUS_CD = 'P' AND Dialer_Tbl_Nme = '5010_5010' ORDER BY ImportRuleDate ASC) UPDATE CTE SET STATUS_CD = 'S'

  • Your first query was updating T_VM_QUEUE where STATUS_CD was equal to the value returned by the subquery used in the where clause. The second one is updating only one row because only one row is returned by the CTE. That's a really cool feature of the CTE, and you can use it to perform deletes as well.

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

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