How do I “mass edit” a field in my mysql database
How do I “mass edit” a field in my mysql database
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?
5 Answers
5
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.
Almost - I've updated the
select + update lines in the code above - you can complete both updates with one command (OR) and include further filters– Rudu
Sep 15 '10 at 19:55
select
update
OR
attempting now- thx
– adam
Sep 15 '10 at 20:50
There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem ERROR: Unknown Punctuation String @ 37 STR: =/ SQL: SELECT * FROM 'users' WHERE (Willing=/0) AND userid<1000 SQL query: SELECT * FROM 'users' WHERE (Willing=/0) AND userid<1000 MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''users' WHERE (Willing=/0) AND userid<1000' at line 1
– adam
Sep 15 '10 at 21:02
SELECT * FROM 'users' WHERE (Willing=/0) AND userid<1000 the value im trying to change is "/0" (not sure how it got that way int he first place) only zero or one was an option
– adam
Sep 15 '10 at 21:03
The following will update a field in all the rows, where the field is currently set to NULL:
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.
'ZIP CODE'
UPDATE `tablename` SET `fieldname` = 'ZIPCODE' WHERE `fieldname` is null;
That's not going to work, unless he's using the literal string "null."
– Paul Schreiber
Sep 15 '10 at 18:31
UPDATE tablename SET fieldname = 'ZIPCODE' WHERE fieldname IS NULL
UPDATE tablename SET fieldname = 'ZIPCODE' WHERE fieldname IS NULL
Late to the party, but for those that's field value is not null this might be the right approach.
UPDATE tablename SET columnname = 'NEW_VALUE' WHERE columnname = 'OLD_VALUE';
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
ok this works bc all the existing fields are null or 0 (i can do it 2x)... what if i want to limit further to just a specific set of users... do i just add "and WHERE userid < 1000" or somethign?
– adam
Sep 15 '10 at 19:21