Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query is updating all records, not one Expand / Collapse
Author
Message
Posted Tuesday, February 25, 2014 9:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 11:48 AM
Points: 6, Visits: 38
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?
Post #1545213
Posted Tuesday, February 25, 2014 11:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 11:48 AM
Points: 6, Visits: 38
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'
Post #1545227
Posted Wednesday, February 26, 2014 5:52 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 3,978, Visits: 2,994
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.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1545348
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse