Change cells based on count

[bbenedict]

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 November 19, 2012 by bbenedict

[justsomeguy]

That's sort of abstract. How do you know which zipcodes to use for each column?

[bbenedict]

Hi,I have chosen a zipcode that i will be using to replace the junk entries. there is just to much data to be percise so i am taking all zipcodes with the lead count of 1 (99% of which is junk) and changing them to a predetermined zip.

[justsomeguy]

If you're trying to clean up the data then I would just run however many update queries you need to catch everything: UPDATE table SET field1='new zip' WHERE field1 LIKE '%--=-==%'

[bbenedict]

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.

[justsomeguy]

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.

[bbenedict]

yes its backed up. I wll give this a try.