0

mysql> WITH 2 extra rows(86321 and 28034) in profile_zipcodes,why is my third query NOT finding them? 

SELECT zipcode from profile_zipcodes;
+---------+
| zipcode |
+---------+
| 01971   |
| 02110   |
| 02119   |
| 02124   |
| 02180   |
| 07086   |
| 07087   |
| 10008   |
| 10019   |
| 10021   |
| 10022   |
| 10024   |
| 10025   |
| 10095   |
| 10098   |
| 10099   |
| 10110   |
| 10112   |
| 10118   |
| 10131   |
| 10152   |
| 10157   |
| 10158   |
| 10159   |
| 10160   |
| 10161   |
| 10162   |
| 10166   |
| 10174   |
| 28034   |
| 33306   |
| 33319   |
| 50301   |
| 62334   |
| 63031   |
| 63131   |
| 63138   |
| 63141   |
| 63366   |
| 63376   |
| 67002   |
| 86321   |
+---------+
42 rows in set

mysql> 

SELECT DISTINCT zip FROM profile_stats;
+-------+
| zip   |
+-------+
| NULL  |
| 01971 |
| 02110 |
| 02124 |
| 02180 |
| 07086 |
| 07087 |
| 10008 |
| 10019 |
| 10021 |
| 10022 |
| 10024 |
| 10025 |
| 10095 |
| 10098 |
| 10099 |
| 10110 |
| 10112 |
| 10118 |
| 10131 |
| 10152 |
| 10157 |
| 10158 |
| 10159 |
| 10160 |
| 10161 |
| 10162 |
| 10166 |
| 10174 |
| 33306 |
| 33319 |
| 50301 |
| 62334 |
| 63031 |
| 63131 |
| 63138 |
| 63141 |
| 63366 |
| 63376 |
| 67002 |
+-------+
40 rows in set (0.00 sec)

mysql> 

SELECT * FROM profile_zipcodes WHERE profile_zipcodes.zipcode NOT IN 
(SELECT DISTINCT zip FROM profile_stats);
Empty set (0.00 sec)

My php code actually runs this query:

DELETE FROM profile_zipcodes WHERE profile_zipcodes.zipcode NOT IN (SELECT DISTINCT zip FROM profile_stats)

Whenever a user changes their zip code or deletes their profile, and it was working fine, then suddenly I started noticing that zip codes with no profiles attached were remaining in the dynamically updated pull down box and debugging showed the query was still being run but the old zips were remaining and testing showed that an identically worded SELECT wasn't finding the orphan zips - it did work for a while, then it stopped?

Karl-Henrik
  • 1,113
  • 1
  • 11
  • 17
user3721389
  • 1
  • 1

1 Answers1

0

Your profile_stats table contains a NULL value on the zip column, while this query that makes use of IN clause will work as expected:

DELETE FROM profile_zipcodes
WHERE
  profile_zipcodes.zipcode IN (SELECT DISTINCT zip FROM profile_stats)

using NOT IN can be a little tricky (please see this question, it's about SQL Sever but the problem is the same).

You can try with this:

DELETE FROM profile_zipcodes
WHERE
  profile_zipcodes.zipcode NOT IN (SELECT DISTINCT zip
                                   FROM profile_stats
                                   WHERE zip IS NOT NULL)
Community
  • 1
  • 1
fthiella
  • 48,073
  • 15
  • 90
  • 106
  • That was exactly it! Thanks much. Originally I used triggers to remove old zips from profile_zipcodes on delete or update from stats, but hosting sites won't let you use triggers so I factored them out and when my code stopped working I thought I was in for a nightmare...fixed the errant rec and modified table to not allow null zips. – user3721389 Jun 09 '14 at 07:48