CODEWITHSUNDEEP
mysqlsql
Baso
Baso

Reputation: 1354

How to set the value of a column to a sql query?

I have two tables x and y I want a column from x to be set to the average of a column from y grouped by a common column.

This what I'v done so far 

update
set x.column2 = (SELECT AVG(NULLIF(column2,0)) 
             FROM y group by column1)
                 on (x.column1 = y.column1)

And I want the value of x.column2 to be updated automatically whenever the value of any row of y.column2 changes.

Note: there is no column have the same name in the two tables.

Upvotes: 0

Views: 59

Answers (2)

Aconcagua
Aconcagua

Reputation: 25536

You will need a trigger - see here.

In your case something like

CREATE TRIGGER name AFTER INSERT ON y FOR EACH ROW BEGIN [above statement] END
CREATE TRIGGER name AFTER UPDATE ON y FOR EACH ROW BEGIN [above statement] END
CREATE TRIGGER name AFTER DELETE ON y FOR EACH ROW BEGIN [above statement] END

I did not try out this, so no guarantee for being free of syntax errors (but should not be).

Upvotes: 0

Chris Trahey
Chris Trahey

Reputation: 18290

UPDATE 
  x
SET 
  x.column2 = (SELECT AVG(NULLIF(column2,0)) 
               FROM y 
               WHERE y.column1 = x.column1)

This will run the subquery once per row in x, but the subquery is limited to the rows in y where column1 matches the current x.column1.

For the curious, the internals of this are a bit deeper. In general, all queries (even sub-queries) return table-like objects ("relation" in relational-speak). If the result has only one row, it can be coerced into a 'row' ("tuple" in relational-speak). If the tuple has only one column, it can be further coerced into the value in that column. That is what is going on here. Additionally, no explicit "group by" is needed, because the WHERE clause limits the subquery to only the rows we want to sum, and so we take advantage of the implied 'group all rows' behavior (analogous to adding GROUP BY y.column1)

After your comment, I wanted to show how you would create a "View" for the same thing, which in MySQL means that the aggregated value is not actually 'stored', but calculated in real-time. This means it is never out of date as you insert into y.

CREATE VIEW vx AS SELECT column1, AVG(NULLIF(column2,0) as avg FROM y GROUP BY y.column1

You can then select from vx and in the background it will run that query.

Upvotes: 1

Related Questions