Jump to content

Change cells based on count


bbenedict
 Share

Recommended Posts

Hey,I am making a demo database and I have some really dirty data that I need to clean up. In the GEO dim I want to change all the Zipcodes with the count of one lead to reflect a zipcode that I can use in a map dashboard. Currently most counts of one contain --=-== and other completely usless informaiton. I think best way is to create a temp view but I can't figure out the code I should use to change the count (1) to whatever zipcode I need. Any ideas would be much appreciated.Thanks!B

Edited by bbenedict
Link to comment
Share on other sites

I would but there are over 60k rows I do not have the time for that. I just want to change any zipcode that has a count of 1 lead to 12345 for example. Then I will have a managable amount to run update queries. It does not have to have a strong relationship with state and country.

Link to comment
Share on other sites

You could do a query to get the list of IDs that have a count of 1, and then another query to update all of those IDs. e.g.: UPDATE table SET zip = '12345' WHERE zip IN (SELECT zip FROM table GROUP BY zip HAVING COUNT(zip) = 1) I don't know if it needs to be said, but make sure you're backing up your database before doing this stuff.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...