SQL query to transfer info between fields?

[fredrikse]

Hi,

I'm in need of a query that can transfer information between imported vB custom fields and newly created XenForo custom fields. The information is text or numeric values.

 

[Jake Bunce]

INSERT INTO xf_user_field_value (user_id, field_id, field_value)
	SELECT user_id, 'new_field_id', field_value
	FROM xf_user_field_value
	WHERE field_id = 'old_field_id'
ON DUPLICATE KEY UPDATE
	field_value = VALUES(field_value);

Then rebuild the user cache:

Admin CP -> Tools -> Rebuild Caches -> Rebuild User Caches

 

[fredrikse]

INSERT INTO xf_user_field_value (user_id, field_id, field_value)
SELECT user_id, 'new_field_id', field_value
FROM xf_user_field_value
WHERE field_id = 'old_field_id'
ON DUPLICATE KEY UPDATE
field_value = VALUES(field_value);

Then rebuild the user cache:

Admin CP -> Tools -> Rebuild Caches -> Rebuild User Caches

This is great. Since I used swedish words for the field names in vBulletin I'm going to create new ones with english field IDs and transfer all information correspondingly.

What would the query look like if I want to transfer information from a custom field to, let's say for instance, the "about" field in XenForo?

 

[Jake Bunce]

What would the query look like if I want to transfer information from a custom field to, let's say for instance, the "about" field in XenForo?

UPDATE xf_user_profile AS up
SET up.about = (
	SELECT ufv.field_value
	FROM xf_user_field_value AS ufv
	WHERE ufv.user_id = up.user_id
	AND ufv.field_id = 'old_field_id'
);