Update multiple columns from subquery

Question

This type of thing has been asked a few times before, but is not quite what I am looking for. I need to SET two rows equal to different parts of a subquery.

I am currently using:

UPDATE records
SET leads=(SELECT COUNT(*) FROM leads_table WHERE leads_table.blah=records.blah),
earnings=(SELECT SUM(amount) FROM leads_table WHERE leads_table.blah=records.blah)

The WHERE statements were obviously simplified...but basically its the same subquery but I don't think I should be running it twice?

I want to do something like...

UPDATE records
SET (leads,earnings)=(SELECT COUNT(*),SUM(amount) FROM leads_table WHERE leads_table.blah=records.blah)

mvermand · Accepted Answer · 2024-02-07 17:27:45Z

48

You can simply join the table in a subquery that do some calculations,

UPDATE  records a
        INNER JOIN
        (
            SELECT  blah, 
                    COUNT(*) totalCount,
                    SUM(amount) totalSum
            FROM    leads_table
            GROUP   BY blah
        ) b ON  b.blah = a.blah
SET     a.leads = b.totalCount,
        a.earnings = b.totalSum

edited Feb 7, 2024 at 17:27

mvermand

6,1678 gold badges50 silver badges79 bronze badges

answered May 10, 2013 at 0:42

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Daddy32 Over a year ago

Nice indeed, elegant and fast.

nbransby Over a year ago

Is it fast? Would it not calc count/sum for every blah even if you are only selecting one?

Siddharth-Soni Over a year ago

needs a comma after the first set

Xtophe Over a year ago

I think it works well but beware if all rows in leads_table referring to a specific row of records are removed, I don't think it will update the field in records back to zero properly, so would need to zero all first.

Collectives™ on Stack Overflow

Update multiple columns from subquery

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related