XF 1.2 Batch update a custom user field

[Marcel]

We ported over about 60,000 users from vBulletin, on which we had a custom field which was a simple checkbox.

In xenForo I set up the same field as a dropdown selection, with yes or no.
I (wrongly) assumed that in xenForo, this field was stored in xf_user_field_value, creating a row with the user_id, field_id, and field_value when the value was set.

Therefore I ran an SQL query to update this table and set the field_value to yes where vbulletin>userid_customfield = 1, else set the field_value to no. I thought this had worked.
A quick check of the batch update users function where this field is 'set to yes', brought back roughly the right amount of users that were set in vb, the same with 'set to no'

Now I read that this custom field information also stored in the xf_user_profile table itself too.

Now I'm a little out of my depth.

I still have access to the old vbulletin database.
What would the relevant SQL query be to update our xen installation, setting this new dropdown field to "yes" where it is checked in the old database, otherwise set it to "no".

It's a field that's also displayed on our postbit too, so that's how I noticed that it wasn't set for alot of people, as it wasn't showing up.

 

[Jake Bunce]

xf_user_field_value is correct. But after making changes in that table you need to rebuild your user info:

Admin CP -> Tools -> Rebuild Caches

That will update the serial cache of custom fields in the other table.

 

[Marcel]

Thanks Jake.

This I aready did, but I think it reset alot of the fields. For example, my own forum profile used to be set to "Yes" but after running the rebuild of the user cache, that field now has neither choice selected.

This was the SQL statement I used initially. We've only been switched for nearly 2 weeks so I can easily run this again from the old database (once this option is chosen by the user, it's rarely changed)

UPDATE newdatabase.xf_user_field_value AS xu
SET xu.field_value = 'Yes'
WHERE (xu.field_id = 'image_editing') AND EXISTS (SELECT * from olddatabase.userfield AS uf WHERE uf.field5 = 1)

 

[Marcel]

Damn, just realised, I realise now this is wrong.
On vb, the field / row always existed for each user.
On xenForo, the row is only created when that option is present / has data, and this statement is just going to update those that have already set it in xenForo.
I need it to not just update it, but also create the row (if needed) and set it to yes.

 

[Marcel]

Nope, I've got no idea. I can't figure out this SQL statement to do that. Can anyone help please?

 

[Jake Bunce]

Try this:

INSERT INTO newdatabase.xf_user_field_value (user_id, field_id, field_value)
	SELECT userid, 'image_editing', IF(field5 = 1, 'Yes', 'No')
	FROM olddatabase.userfield
ON DUPLICATE KEY UPDATE
	field_value = VALUES(field_value);

 

[Marcel]

I think that seems to have done the trick. Mr Bunce to the rescue once again!