Portal Home > Knowledgebase > Articles Database > MySQL: Copy data from field A to field B only if field B is empty and vice-versa?
MySQL: Copy data from field A to field B only if field B is empty and vice-versa?
| Posted by damainman, 05-21-2010, 02:42 PM |
| Greetings,
I have simple problem I'm trying to resolve for a friend of mine. Do to a script they were using and the way the template worked.. they mixed around some data and now we are trying to put everything in one field.
The table name is:
my_addresses
In that table are two fields:
zipcode
pobox
I need both fields to have identical data. Some listings have data in zipcode while other listings have data in pobox and other listings have data in both. Other listings have both fields blank.
----------
1. Both fields have similar data, but at one point the zipcode field had a length limit so it cut off some of the data. (ie: P.O. Box 157980 saved into the database as P.O. Box 1)
Other listings just have "P.O. Box" with no numbers.
2. I was thinking of creating a 3rd field, so I can dump that data in that field and then just copy that data back into the zipcode field. Only because logically to me this seemed like it might be easier.
3. In the 3rd field I was going to import all the zipcode data. Then import all the pobox data. Only overwriting existing with the field data being imported is not empty or contains a full P.O. Box number.
4. So blank field entries can be overwritten, and fields that contain "P.O. Box" or "P.O. Box 1"(one number only) can be overwritten.
5. If the field contains 10 characters or less (ie: "P.O. Box 1") it should be over written, unless the field data being imported is blank or contains less than 10 characters itself. If it contains more than 10 characters then it probably contains the correct P.O. Box and shouldn't be overwritten.
6. After importing the data into the 3rd field, I want to copy it back into the zipcode field.
Thank you in advance.
|
| Posted by petteyg359, 05-21-2010, 04:24 PM |
| Put zip codes in third field
UPDATE my_addresses zipcode = thirdfield WHERE CHAR_LENGTH(zipcode) <= 10 AND CHAR_LENGTH(thirdfield) > 10
-- Put pobox in third field
UPDATE my_addresses pobox = thirdfield WHERE CHAR_LENGTH(pobox) <= 10 AND CHAR_LENGTH(thirdfield) > 10
Since you seem to have all the data intact in another format already, though, I would think a simple script that simply overwrites everything would be much quicker and easier than
Create additional fieldPopulating field with zipsConditional copy fieldErase fieldPopulating field with poboxsConditional copy fieldDelete field
|
| Posted by damainman, 05-21-2010, 04:50 PM |
| Thank you for your reply
I'm not sure how to write a script to accomplish that which is why i figured maybe creating a 3rd field and merging the fields into it with particular conditions might be the best way to do it.
Someone suggest I do the following, and I wanted to know if you thought your way might be more useful to me:
|
Add to Favourites 
Print this Article
Also Read
