SQL Update command getting more than expected

Question

I'm trying to do an update on my SQL DB (2008 R2), but for some reason it's updating more than I expected. I think there's an issue with my where, join, and update commands but I'm having trouble finding info on scope and order of operations. Fortunately, I'm practicing on DB backups that are restored before I make the change in the production one!

I've found this link that seems similar (join and update), but it's tough for me to apply it to my case.

For the testUnitCount of 15578, I'm trying to change the unitNo from 05101 to 05088. For some reason, it's moving the unitNo for all of them for this TestNumber. It's an OLAP DB (Cubes), so it may seem more complicated with all the tables than people are used to. I thought I had the command correct this time:

SELECT     DashboardData.TestNumber, TestUnits.unitNo, TestUnitCounts.testUnitCount
FROM         DashboardData INNER JOIN
                      TestUnitCounts ON DashboardData.TestUnitCountID = TestUnitCounts.testUnitCountID INNER JOIN
                      MeasurementData ON TestUnitCounts.testUnitCountID = MeasurementData.TestUnitCountID INNER JOIN
                      TestUnits ON DashboardData.TestUnitID = TestUnits.testUnitID AND TestUnitCounts.testUnitID = TestUnits.testUnitID AND 
                      MeasurementData.TestUnitID = TestUnits.testUnitID
                      where unitNo='05101'
                      AND TestNumber='1024'
                      AND TestUnitCounts.testUnitCount='15578'
                      order by testUnitCount asc
                      UPDATE TestUnits
                      SET unitNo='05088' where unitNo='05101'

Does anyone know what the command needs to be changed to so I just change the testUnitCount of 15578 for unitNo 05101->05088 for Test 1024? Why is it changing all of them for that TestNumber?

Here is my CTE attempt for the same thing. I'm getting 0 row(s) affected when I execute it:

Use OLAP05132014C
GO


WITH QueryName_CTE (unitNo,TestNumber,testUnitCount)
AS
(
SELECT     DashboardData.TestNumber, TestUnits.unitNo, TestUnitCounts.testUnitCount
FROM         DashboardData INNER JOIN
                      TestUnitCounts ON DashboardData.TestUnitCountID = TestUnitCounts.testUnitCountID INNER JOIN
                      MeasurementData ON TestUnitCounts.testUnitCountID = MeasurementData.TestUnitCountID INNER JOIN
                      TestUnits ON DashboardData.TestUnitID = TestUnits.testUnitID AND TestUnitCounts.testUnitID = TestUnits.testUnitID AND 
                      MeasurementData.TestUnitID = TestUnits.testUnitID
                      where unitNo='05101'
                      AND TestNumber='1024'
                      AND TestUnitCounts.testUnitCount='15578'
)
Update QueryName_CTE


                      SET unitNo='05088' where unitNo='05101'

Are you only trying to update rows that are involved in the Select. The way it is above, those are 2 distinct statements.....nothing to do with each other. You may want to search CTE, which allows you to update the items in the smaller group. — granadaCoder, May 16 '14 at 13:32
Yes, I'm trying to only update rows in the Select. I never heard of searching CTE. What do you mean? — Michele, May 16 '14 at 13:41

user1948635 · Answer 1 · 2014-05-16T14:06:57.280

As stated in the comment above, the update is currently being run seperately. Change the query to be an update instead of a select.

Change your query to be -

    UPDATE TestUnits
    SET TestUnits.unitNo = '05088'
    FROM         DashboardData INNER JOIN
                          TestUnitCounts ON DashboardData.TestUnitCountID = TestUnitCounts.testUnitCountID INNER JOIN
                          MeasurementData ON TestUnitCounts.testUnitCountID = MeasurementData.TestUnitCountID INNER JOIN
                          TestUnits ON DashboardData.TestUnitID = TestUnits.testUnitID AND TestUnitCounts.testUnitID = TestUnits.testUnitID AND 
                          MeasurementData.TestUnitID = TestUnits.testUnitID
                          where unitNo='05101'
                          AND TestNumber='1024'
                          AND TestUnitCounts.testUnitCount='15578'
                          order by testUnitCount asc

It's not liking that syntax with the first line. Invalid object name. It's in that table, though. — Michele, May 16 '14 at 13:47
It looks like that changed the unitNo for testUnitCount of '5' as well as '15578'. It's still having the same issue. Backup Restore time! Next try... — Michele, May 16 '14 at 15:25

granadaCoder · Answer 2 · 2014-05-16T19:09:39.603

Here is a Northwind CTE example.

I think this is what you're trying to do. (update rows that are in the select query, not the entire table)

Here is the MSDN link for Sql Server 2008, R2.

http://technet.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx

Here is the example code....

Use Northwind
GO


Declare @CustomerID char(5)
Select @CustomerID = 'VINET'

print 'Pre Look'
Select o.CustomerID , o.ShipVia 
    FROM 
        dbo.Orders o where o.CustomerID = @CustomerID

/* Now the CTE.  The CTE gets a result based on the specific customerid */

;WITH cteOrdersForOneCustomer (OrderID,CustomerID,EmployeeID,OrderDate,RequiredDate,ShippedDate,ShipVia,Freight,ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry )
 AS
 (
    SELECT o.OrderID,o.CustomerID,o.EmployeeID,o.OrderDate,o.RequiredDate,o.ShippedDate,o.ShipVia ,o.Freight,o.ShipName,o.ShipAddress,o.ShipCity,o.ShipRegion,o.ShipPostalCode,o.ShipCountry 
    FROM 
        dbo.Orders o where o.CustomerID = @CustomerID
 )

/* Now I update the rows in the CTE, not the entire table */
Update cteOrdersForOneCustomer
    Set ShipVia = 1 where ShipVia = 3


print 'Post Look'
Select o.CustomerID , o.ShipVia 
    FROM 
        dbo.Orders o where o.CustomerID = @CustomerID

APPEND:

Use this tactic to debug what is going on with the CTE

declare @CurrentRowCount int 


WITH QueryName_CTE (unitNo,TestNumber,testUnitCount)
AS
(
SELECT     DashboardData.TestNumber, TestUnits.unitNo, TestUnitCounts.testUnitCount
FROM         DashboardData INNER JOIN
                      TestUnitCounts ON DashboardData.TestUnitCountID = TestUnitCounts.testUnitCountID INNER JOIN
                      MeasurementData ON TestUnitCounts.testUnitCountID = MeasurementData.TestUnitCountID INNER JOIN
                      TestUnits ON DashboardData.TestUnitID = TestUnits.testUnitID AND TestUnitCounts.testUnitID = TestUnits.testUnitID AND 
                      MeasurementData.TestUnitID = TestUnits.testUnitID
                      where unitNo='05101'
                      AND TestNumber='1024'
                      AND TestUnitCounts.testUnitCount='15578'
)

--Debug Level 1, see if anything is in that table
Select * from QueryName_CTE

/*
--Debug Level 2, see if what you're looking for is in that table
Select * from QueryName_CTE where unitNo='05101'
*/

/*
--After debug 1 and 2....do the update
Update QueryName_CTE
    SET unitNo='05088' where unitNo='05101'
*/  

Select @CurrentRowCount = @@ROWCOUNT

print 'Row Count After "Debug Level 2" or  "After debug 1 and 2....do the update"'
print @CurrentRowCount
print ''

Me neither. I guess when it says "take this to a discussion", we should have listened. — granadaCoder, May 22 '14 at 14:58

Michele · Accepted Answer · 2014-05-22T13:04:58.233

I thought I'd update this since we got it working. My co-worker used a different approach (worked) and I'm not exactly sure why it didn't work (directly) with the parameters given for the problem, and the other values had to be used, but this is what fixed it.

So the tables involved look like this:

PQdata table name ---------DashboardData table name----------TestUnits table name
key pqID                   key pqID                          key testUnitID
TestIndex                  TestIndex                         unitNo
                           TestNumber                           |
                           TestUnitID                           |
                               |                                |
                            TestUnitCounts table name------------
                            key testUnitCountID
                            testUnitID
                            testUnitCount

I went back and documented it based on his queries:

•He needed to look at it first:

SELECT     PQdata.pqID, PQdata.TestIndex, TestUnits.unitNo, TestUnits.testUnitID, DashboardData.TestNumber, TestUnitCounts.testUnitCount
FROM         DashboardData INNER JOIN
                      TestUnitCounts ON DashboardData.TestUnitCountID = TestUnitCounts.testUnitCountID INNER JOIN
                      TestUnits ON DashboardData.TestUnitID = TestUnits.testUnitID AND TestUnitCounts.testUnitID = TestUnits.testUnitID INNER JOIN
                      PQdata ON DashboardData.pqID = PQdata.pqID
                      where TestNumber='1024'
                      AND unitNo='05101'
                      AND testUnitCount='15578'

•This provided pqid corresponding to TestNumber: 10204

•Then he had to find testUnitID for unitNo 05088:

SELECT     PQdata.pqID, PQdata.TestIndex, TestUnits.unitNo, TestUnits.testUnitID, DashboardData.TestNumber, TestUnitCounts.testUnitCount
FROM         DashboardData INNER JOIN
                      TestUnitCounts ON DashboardData.TestUnitCountID = TestUnitCounts.testUnitCountID INNER JOIN
                      TestUnits ON DashboardData.TestUnitID = TestUnits.testUnitID AND TestUnitCounts.testUnitID = TestUnits.testUnitID INNER JOIN
                      PQdata ON DashboardData.pqID = PQdata.pqID
                      where TestNumber='1024'
                      AND unitNo='05088'

•Therefore need to change it to testUnitID: 2971

•Plus he had to find original testUnitID similarly: 2970

•Therefore, update needed looks like this:

UPDATE DashboardData SET testUnitID = 2971 WHERE testUnitID = 2970 AND pqID = 10204

It was a more round-about approach than I had tried (mine didn't work).

SQL Update command getting more than expected

3 Answers3