refering a calculated value in another column

Question

I'm trying to make a calculated value to show in another column in another table. Can someone please explain why this doesn't work

CREATE TABLE #Medition (ID int,AVG decimal(18,4))
INSERT INTO #Medition (ID, AVG) 
SELECT ID, SUM(125Hz + 250Hz + 500Hz + 750Hz + 1000Hz + 1500Hz + 2000Hz + 3000Hz + 4000Hz + 6000Hz + 8000Hz)/11 AS AVG FROM tonvarden 
UPDATE matningar  SET matningar.tonmedelvarde =
#Medition.AVG FROM matningar INNER JOIN #Medition ON matningar.ID =#Medition.ID
 DROP TABLE #Medition

I am getting this error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO #Medition (ID, AVG) SELECT ID, SUM(125Hz + 250Hz + 500Hz + 750Hz + ' at line 2

There's a few syntax errors in your code, but the answer to your question is that you are structuring your `UPDATE` with `JOIN` incorrectly: http://stackoverflow.com/questions/15209414/mysql-update-join — wils484, Jun 25 '14 at 23:45

Fabricator · Accepted Answer · 2014-06-27T00:18:03.850

0

No need to create a temporary table to do this.

UPDATE matningar a
join tonvarden b on a.ID = b.ID
set a.tonmedelvarde = (`125Hz` + `250Hz` + `500Hz` + `750Hz` + `1000Hz` + 
                          `1500Hz` + `2000Hz` + `3000Hz` + `4000Hz` + `6000Hz` + 
                          `8000Hz`)/11;

If you would like to update matningar whenever a new row is inserted into tonvarden, then you can create the following trigger:

create trigger update_matningar before insert on tonvarden
for each row 
update matningar 
set tonmedelvarde = 
    (new.`125Hz` + new.`250Hz` + new.`500Hz` + new.`750Hz` 
        + new.`1000Hz` + new.`1500Hz` + new.`2000Hz` 
        + new.`3000Hz` + new.`4000Hz` + new.`6000Hz` 
        + new.`8000Hz`)/11
where id = new.id;

edited Jun 27 '14 at 00:18

answered Jun 26 '14 at 01:14

Fabricator

12,722
2
27
40

I am getting error #1111 - Invalid use of group function – Shoxxer Jun 26 '14 at 02:48
@Shoxxer, oops. `SUM` shouldn't be in there. Please try the updated answer. – Fabricator Jun 26 '14 at 02:51
Thank you it is working, however I'm wondering if there is a way to make the value to show automatically on the column as soon as a new insert of data is done. Right now I have to enter this update statement every time after I created a row. Is it possible to make it insert automatically? Maybe as a trigger? – Shoxxer Jun 26 '14 at 18:34
@Shoxxer, do you mean updating `matningar` whenever a new row is inserted to `tonvarden`? – Fabricator Jun 26 '14 at 19:48
Yes, exactly like that – Shoxxer Jun 26 '14 at 23:32
Thank you, is there a way to make it conditional like you did in the first update statement where it matched the ID together "a.ID = b.ID". Can this be done with with a trigger? – Shoxxer Jun 27 '14 at 00:14

refering a calculated value in another column

1 Answers1