How do I "mass edit" a field in my mysql database

Question

I have a field in my users table and I want to change about 1000 entries from "null" to a specific zip code... is there a query I can run?

Answer 1

UPDATE `tablename` SET `fieldname` = 'ZIPCODE' WHERE `fieldname` is null;

Answer 2

The following will update a field in all the rows, where the field is currently set to NULL:

UPDATE your_table 
SET    your_field_name = 'ZIP CODE' 
WHERE  your_field_name IS NULL;

Simply substitute the 'ZIP CODE' value with the string that you require.

Answer 3

You may want to preview what's getting updated first:

SELECT * FROM yourtable WHERE specificfield IS NULL

The update script is as simple as:

UPDATE yourtable SET specificfield='$newvalue' WHERE specificfield IS NULL

Where:
yourtable - the name of the table you want with cells you want to update.
specificfield - the name of the field you'd like to update.
$newvalue - the new value for the field (in quotes since it's likely a string - my need to escape that string).

note:: this only works if all the fields are going to get the same value (eg. specificfield='00000')

Updated: (per user comments)

SELECT * FROM yourtable 
WHERE (specificfield IS NULL OR specificfield=0) AND userid<1000

UPDATE yourtable SET specificfield='$newvalue' 
WHERE (specificfield IS NULL OR specificfield=0) AND userid<1000

The where statement from your a select is the same as used in the update - so you can tailor a where statement for exactly the conditions you need.

Answer 4

UPDATE tablename SET fieldname = 'ZIPCODE' WHERE fieldname IS NULL