XF 1.5 How to move custom field content to XF Gender field?

[Alpha1]

In vb we use a custom field for gender. I would like to move the content of this field to the XF default gender field.
What query should I use? (mind that it concerns a big board)
@Jake Bunce

Would this work?

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

 

[Chris D]

Thinking ahead... In XF2 we don't have a gender field by default and move it to a custom field for upgrades so I wonder if it's worth moving it now or waiting until it's a custom field anyway.

 

[Alpha1]

I do not expect to be able to use XF2 this year, due to many large addon rewrites. Including XF1 addons that are currently in production.

 

[Chris D]

Fair enough. That query looks like it should do it. You might want to do this though to prevent overwriting a value there if it already exists:

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

 

[Alpha1]

Thank you @Chris D

 

[Alpha1]

weird. There is no up.gender so the query fails.

 

[Chris D]

Sorry, should be:

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

Worth noting that in XF2 we have removed the default gender field but we migrate it to a custom field automatically on upgrade.

 

[Alpha1]

Worked like a charm. many thanks @Chris D !