XF 1.4 Changing field_id under user_field_value after import

[Movie Prop Sites]

We recently imported from ip.board to Xenforo and brought over a single custom user field with a field id of "first name".

I am not sure exactly what the issue is (possibly the space?) but this custom user field has issues. I can't update the Title or the description for it from the Admin Console.

With that in mind, I have created a new custom user field with the field id of "firstname"

What I want to do is move all the old data that is under "first name" to now be under "firstname."

Seems simple enough. This data is held in the table xf_user_field_value.

It seems all I need to do is change the field_ids in that table from one to the other. While this seems like a simple SQL replace function, because the field_id is a varbinary, I can't seem to determine what query to run to change one to another. I have run every variation I could find, but there is always a syntax error. This should be pretty simple... but I just can't seem to do it. Any info would help.

For what it is worth:
The value of the old field_id is 6669727374206e616d65
The value of the new field_id is 66697273746e616d65

I am assuming they are so similar because the only difference between the names is a space.

 

[Mike]

Yeah that extra "20" is a space (20 is a space in ASCII in hex; yes I know this off the top of my head and yes I know that's sad ). This is the first I've heard of a field being created like that from an import.

Unfortunately, changing custom field values like that is difficult as there are multiple tables that need to be updated. You may be able to get away with changing the field ID on the rows in the xf_user_field_value table and rebuilding users in the control panel, but this isn't something we'd strictly support/recommend. If you're going to run a query, take a backup first.

The query format may be:

UPDATE xf_user_field_value SET field_id = 'new_value' WHERE field_id = 'old_value';

 

[Movie Prop Sites]

Thanks and I totally understand. Let me see if that will work.

 

[Movie Prop Sites]

Oh and I caught the 20 as well. Sadly, seems like we are on the same level of shame... the difference being you have answers to the problems and I was just running in circles.

 

[Movie Prop Sites]

Ran the query but ran into issues as using single quotes seems to make it think it is a string, but based on the earlier research I did I was able to mod it just a bit and it worked beautifully! Thank you so much! This was driving me nuts!

UPDATE xf_user_field_value SET field_id = 0x66697273746e616d65 WHERE field_id = 0x6669727374206e616d65;