Update table based on aggregation from another table

Question

I have a table t1 like this:

Names Drug Tom A Tom A Tom B Lily B Lily B

and a table t2 like this:

Names Drug COUNT(Drug) Tom A Tom B Tom C Lily A Lily B

I want to insert into the COUNT(drug) column of t2 the data from t1 after grouping by drug and name. The desired output of t2 looks like this:

Names Drug COUNT(Drug) Tom A 2 Tom B 1 Tom C 0 Lily A 0 Lily B 2

I am thinking about creating another table t3 with the GROUP BY drug, name data from t1 and then UPDATE t2 based on t3 but it is quite time-consuming.

AdamMc331 · Accepted Answer · 2015-05-28 15:22:50Z

Anytime you have a complicated problem like this, you should always try to break it up into smaller pieces that you can solve, and put them back together.

For example, I would start by getting the name/drug counts from the first table like this:

SELECT name, drug, COUNT(*) AS numDrugs FROM t1 GROUP BY name, drug;

Then, you can use this as a subquery when updating t2 by joining the two tables on the condition that name and drug match. You should use a leftjoin, and coalesce function to replace the values that don't exist with 0:

UPDATE t2 LEFT JOIN( SELECT name, drug, COUNT(*) AS numDrugs FROM t1 GROUP BY name, drug) t1 ON t1.name = t2.name AND t1.drug = t2.drug SET drugCount = COALESCE(numDrugs, 0);

Here is an SQL Fiddle example.

Community · Accepted Answer · 2017-05-23 11:51:09Z

2

I think this question can help you: specific mysql update based on group by data

You can do something like this:

UPDATE t2 LEFT JOIN ( SELECT name, drug, count(*) num FROM t1 GROUP BY name, drug ) r ON t2.name = r.name AND t2.drug = r.drug SET t2.count_drag = COALESCE(r.num, 0);

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered May 28, 2015 at 15:25

flavi

731 silver badge6 bronze badges

2 Comments

AdamMc331 Over a year ago

Note that it's possible a name,drug pair exists in t1 but not t2. For example, in OP's sample data, you will never update the rows for (Tom, C) and (Lily, A). You should try a LEFT JOIN.

AdamMc331 Over a year ago

Looks good! The only thing it's still missing is that the left join will mean r.num is null for those values, not 0. Consider adding a check using IF() or IFNULL() or COALESCE(). Any would work, but I prefer the last one.

Collectives™ on Stack Overflow

Update table based on aggregation from another table

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related