• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.1 Mass move "Location" info from imported custom field, to XF *stock* 'Location' field?


Formerly CyclingTribe
When I import from IP.Board it creates my custom fields, one of which is "Location".

XF has its own built-in Location field so how would I mass move the data from my custom field to the built-in XF one?

Shaun :D

Jake Bunce

XenForo moderator
Staff member
Run this query on your database:

UPDATE xf_user_profile AS up
SET up.location = (
	SELECT ufv.field_value
	FROM xf_user_field_value AS ufv
	WHERE ufv.user_id = up.user_id
	AND ufv.field_id = 'customfield'
You just need to enter the field_id of your custom location field.


Well-known member
@Jake Bunce Several thousands of members have already filled in the default field on my site. Is there a way to copy the content of the custom field into the default field unless there already is a value in the default field?


Well-known member
@Chris D
I tried using the query you provided for the gender field and adapted it to location field. The name of my custom user field is 'location'. I tried this query:

UPDATE xf_user AS u
SET u.location = (
SELECT ufv.field_value
FROM xf_user_field_value AS ufv
WHERE ufv.user_id = u.user_id
AND ufv.field_id = 'location'
WHERE u.location = '';

Unfortunately the result was:
Unknown column 'u.location' in 'where clause'


Well-known member
Unfortunately the above added the drop down names instead of the values. So it shows canada_2 instead of Canada.
I had not realized this as it was quite some years ago I set this up. It should be easy to fix. Would this query be correct to fix it?

UPDATE xf_user_profile AS u
SET u.location = 'Canada'
WHERE u.location = ´canada_2´;