CODEWITHSUNDEEP
mysqlselectjoinsql-update
Datacrawler
Datacrawler

Reputation: 2876

Update, Join and Select 2 tables to calculate a formula

I have an 'Amount' per day shown on tableB :

enter image description here

And I want to calculate the amount per 'Description' (they are 5 in total) in tableA:

enter image description here

So, my formula should be:

tableA.Amount = tableB.Amount * tableA.Numbers / sum(tableA.Numbers) 
where tableA.date = tableb.date
group by tableA.Description

In mySQL I am using this code:

update tableA
join tableB
on tableB.date = tableA.date  
set tableA.amount = tableB.amount * (select tableA.Numbers from tableA group by tableA.date, tableA.Description) / sum(tableA.Impressions)
where tableA.campaign = 'Autumn'
;

But it does not work.

Upvotes: 0

Views: 135

Answers (2)

Don
Don

Reputation: 102

If the syntax is modified as in the example below, it probably should work. It would be helpful to fix if the error is provided.

UPDATE table1 SET column1 = (SELECT expression1 FROM table2 WHERE conditions) WHERE conditions;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can move the logic to the from clause. It is a little hard to figure out exactly what you want to do, but I think it is something like this:

update tableA a join
       tableB b
       on b.date = a.date join 
       (select a.date, sum(a.Numbers) as sumn
        from tableA a
        group by a.date
       ) da
       on a.date = da.date join
    set a.amount = b.amount * a.Numbers / da.sumn
where a.campaign = 'Autumn';

I have no idea what the filter on campaign is doing, because it is not in your sample data.

Upvotes: 1

Related Questions