When importing from vBulletin the user profile Interests field (field3) was moved to about, along with field1, in XF. What SQL query would I have to run if I want to move all the "about" data from xf_user_profile to a custom user field?
Unfortunately, this will be fairly difficult. Pulling data out like that from a simple query isn't really viable; it'd need PHP code to go along with it. (The PHP code would really be needed to ensure the correct values are inserted into the DB as well.)
Well, it looks like I'll have to scrub two different forums and start again. It looks like I'll have to make the changes on the source vBulletin databases then try importing again. Then re-set all the permissions and fix the numerous other post import things I had previously done before realising what the importer does with profile fields 1 - 4 from vBulletin. It would have been useful to have known that the vBulletin importer merged profile fields 1 and 3 and put them into about, irrespective what those fields were being used for or if they had been customised in vBulletin or if they were previously compulsory fields.
As fields are only identified by number, we've assumed 1 - 4 are the "standard" field and mapped them as such. Normally, with the way MySQL does IDs, truly custom fields would start from 5 so it should be correct in the absolute vast majority of cases. (Field 2 and 4 are mapped as location and occupation.)
It did maintain the IDs. On one of our vBulletins we weren't using field 1 and 3 as stock, as they are easy to edit in vB, so putting them both into about wasn't ideal. On another we just didn't want them combined. In one case we also required the occupation field to be mandatory as the site is for those working in a particular niche.