SQL Perform an Action on Every Row that Meets Condition
I am currently trying to average rent over a particular zip code wildcard. For example, if the zipcode for 20000 has rent value 0, it will instead insert the average rent for (non-zero) zipcodes matching the '20%' wildcard. Currently, however, it is averaging all of the rents, regardless of the wildcard. Here is what I am getting:
rent zip 2375 20000 1000 20001 2000 20002 5000 30000 1500 20003 2375 30001
but this is what I want:
rent zip 1500 20000 1000 20001 2000 20002 5000 30000 1500 20003 5000 30001
Here is my current query:
SELECT IF(tmp.rent=0, (select AVG(rent) from tmp where rent > 0 and zip LIKE CONCAT(substr(zip,1,2),'%')), tmp.rent) as rent, zip from tmp
won't this expression evaluate to TRUE for all non-null values of zip, in other words, "all of them" ?
zip LIKE CONCAT(substr(zip,1,2),'%'))
Seems like you want to qualify one of those the column references so it refers to a column from the outer query. It's good practice to qualify all column references.
SELECT IF( t.rent=0 , ( SELECT AVG(s.rent) FROM tmp s WHERE s.rent > 0 AND s.zip LIKE CONCAT(SUBSTR(t.zip,1,2),'%') ) , t.rent ) AS rent , t.zip FROM tmp t