Menu
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)
723
If you want compare two or more columns. you must write a compound WHERE clause using logical operators Multiple-column subqueries enable you to combine duplicate WHERE conditions into a single WHERE clause.
SQL: Multiple Column SubqueriesYou can write subqueries that return multiple columns. The following example retrieves the order amount with the lowest price, group by agent code.
We can update multiple columns by specifying multiple columns after the SET command in the UPDATE statement. The UPDATE statement is always followed by the SET command, it specifies the column where the update is required.
Multiple-row subqueries are nested queries that can return more than one row of results to the parent query. Multiple-row subqueries are used most commonly in WHERE and HAVING clauses. Since it returns multiple rows, it must be handled by set comparison operators (IN, ALL, ANY).
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
