CODEWITHSUNDEEP
sql-server-2008
Rick Hodder
Rick Hodder

Reputation: 2252

Update Query not saving correct value

I am trying to create an UPDATE query that works off of a query

There is a 1->M relationship between ChargeTransaction and ChargeError ChargeBody is a TEXT field containing json.

When I run the inner query I get a distinct list of chargetransactionids and account ids (1 2 and 3), but when I put it into an UPDATE statement, it assigns an accountid of 3 to all of the chargetransactions. 

UPDATE ChargeTransaction
    SET AccountId= a.AccountID
FROM 
    (   
        SELECT ChargeTransactionId, AccountID
        FROM 
        (
            SELECT 
                  [ChargeTransactionId],
                  CASE WHEN ChargeBody LIKE '%"ReceivingFacility":"998001"%' THEN 1 
                       WHEN ChargeBody LIKE '%"ReceivingFacility":"998002"%' THEN 2
                       WHEN ChargeBody LIKE '%"ReceivingFacility":"998003"%' THEN 3
                       ELSE 1
                  END AS AccountId,
                  ChargeBody
              FROM [ChargesDashboard].[dbo].[ChargeError]
         ) b
         GROUP BY ChargeTransactionId, AccountId
     ) a
WHERE ChargeTransactionId=a.ChargeTransactionId ;

Upvotes: 0

Views: 38

Answers (2)

Rick Hodder
Rick Hodder

Reputation: 2252

Turns out the problem was that the ChargeTransaction table doesn't have a ChargeTransactionId field, it has an Id field

UPDATE ChargeTransaction
    SET AccountId= a.AccountID
FROM 
    (   
        SELECT ChargeTransactionId, AccountID
        FROM 
        (
            SELECT 
                  [ChargeTransactionId],
                  CASE WHEN ChargeBody LIKE '%"ReceivingFacility":"998001"%' THEN 1 
                       WHEN ChargeBody LIKE '%"ReceivingFacility":"998002"%' THEN 2
                       WHEN ChargeBody LIKE '%"ReceivingFacility":"998003"%' THEN 3
                       ELSE 1
                  END AS AccountId,
                  ChargeBody
              FROM [ChargesDashboard].[dbo].[ChargeError]
         ) b
         GROUP BY ChargeTransactionId, AccountId
     ) a
WHERE ChargeTransaction.Id=a.ChargeTransactionId ;

Upvotes: 0

John Sobolewski
John Sobolewski

Reputation: 4572

This may not be the most elegant but I think it would work. I think the problem is that you wanted a join to your derived table.

with myCte as
(
SELECT ChargeTransactionId, AccountID
        FROM 
        (
            SELECT 
                  [ChargeTransactionId],
                  CASE WHEN ChargeBody LIKE '%"ReceivingFacility":"998001"%' THEN 1 
                       WHEN ChargeBody LIKE '%"ReceivingFacility":"998002"%' THEN 2
                       WHEN ChargeBody LIKE '%"ReceivingFacility":"998003"%' THEN 3
                       ELSE 1
                  END AS AccountId,
                  ChargeBody
              FROM [ChargesDashboard].[dbo].[ChargeError]
         ) b
         GROUP BY ChargeTransactionId, AccountId
)

UPDATE C
set AccountID = myCte.AccountID 
from ChargeTransaction C
    inner join myCte on C.ChargeTransactionId  = myCte.ChargeTransactionId

Upvotes: 2

Related Questions