Geek Answers Handbook

Removing outliers using mysql using standard deviation

I use the following query to remove emissions (1.5 times the sd).

DELETE FROM sv_condition_sw WHERE snow_mountain > ( SELECT AVG(snow_mountain)+1.5*STDDEV(snow_mountain) FROM sv_condition_sw WHERE lud='2012-11-28' AND res_id=769) AND lud='2012-11-28' AND res_id=769

However, it gives this error:

Request: delete FROM sv_condition_sw WHERE snow_mountain > (SELECT AVG(snow_mountain)+1.5*STDDEV(snow_mountain) FROM sv_condition_sw WHER...

 Error Code: 1093 You can't specify target table 'sv_condition_sw' for update in FROM clause

I do not know what it means.

0
source share
1 answer

You can trick MySQL into doing this with another subquery

 DELETE FROM sv_condition_sw WHERE snow_mountain > (select * from (SELECT AVG(snow_mountain)+1.5*STDDEV(snow_mountain) FROM sv_condition_sw WHERE lud='2012-11-28' AND res_id=769) x) AND lud='2012-11-28' AND res_id=769
+2
source

More articles:


All Articles