CODEWITHSUNDEEP
sqlgroup-bysumdistinct
elzaer
elzaer

Reputation: 729

Update using Distinct SUM

I have found a few good resources that show I should be able to merge a select query with an update, but I just can't get my head around of the correct formatting.

I have a select statement that is getting info for me, and I want to pretty much use those results to Update an account table that matches the accountID in the select query.

Here is the select statement:

SELECT DISTINCT SUM(b.workers)*tt.mealTax as MealCost,b.townID,b.accountID
FROM buildings AS b
INNER JOIN town_tax AS tt ON tt.townID = b.townID
GROUP BY b.townID,b.accountID

So in short I want the above query to be merged with:

UPDATE accounts AS a
SET a.wealth = a.wealth - MealCost

Where MealCost is the result from the select query. I am sure there is a way to put this into one, I just haven't quite been able to connect the dots to get it to run consistently without separating into two queries.

Upvotes: 0

Views: 1396

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

First, you don't need the distinct when you have a group by.

Second, how do you intend to link the two results? The SELECT query is returning multiple rows per account (one for each town). Presumably, the accounts table has only one row. Let's say that you wanted the average MealCost for the update.

The select query to get this is:

SELECT accountID, avg(MealCost) as avg_Mealcost
FROM (SELECT SUM(b.workers)*tt.mealTax as MealCost, b.townID, b.accountID
      FROM buildings AS b INNER JOIN
           town_tax AS tt
           ON tt.townID = b.townID
      GROUP BY b.townID,b.accountID
     ) a
GROUP BY accountID

Now, to put this into an update, you can use syntax like the following:

UPDATE accounts
    set accounts.wealth = accounts.wealth + asum.avg_mealcost
    from (SELECT accountID, avg(MealCost) as avg_Mealcost
          FROM (SELECT SUM(b.workers)*tt.mealTax as MealCost, b.townID, b.accountID
                FROM buildings AS b INNER JOIN
                     town_tax AS tt
                     ON tt.townID = b.townID
                GROUP BY b.townID,b.accountID
               ) a
          GROUP BY accountID
         ) asum
    where accounts.accountid = asum.accountid

This uses SQL Server syntax, which I believe is the same as for Oracle and most other databases. Mysql puts the "from" clause before the "set" and allows an alias on "update accounts".

Upvotes: 1

Related Questions