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

XF 1.5 SQL: Copying field value with conditional

Sperber

Active member
#1
Hey guys,

as most of us I´ve migrated from vBulletin to XenForo. In vBulletin we had a custom field in the db and I need those values now inserted into another field in the xf_user_profile (affected users: roundabout 40.000). I know that it´s totally out of the scope of a normal support request, but I have so less experience in sql manipulation, that I´m afraid to break the database with a misformatted query, that it appears wise to ask for some help.

I want to copy the value of

- table: xf_user_field_value
- field_id: abc

into

- table: xf_user_profile
- field_id: xyz

but IF ONLY the conditional

- table: xf_user_profile, field_id: xyz IS EMPTY and NO value existing in there (to prevent overwriting of more actual data)

is met.

May be I´m too anxious and should give the query samples on stackoverflow a try - but I´ve crashed the DB once with that and since then I guess I´m a liitle bit to cautious and feeling like I`ld have to perform an open heart surgery ;)

You would really do me a huge favor, if you could help me out with that query string for that operation.

Regards,
Sperber.
 

Jake Bunce

XenForo moderator
Staff member
#2
Code:
UPDATE xf_user_profile AS up
SET up.xyz = (
   SELECT ufv.field_value
   FROM xf_user_field_value AS ufv
   WHERE ufv.user_id = up.user_id
   AND ufv.field_id = 'abc'
)
WHERE up.xyz = '';
That should do it. Backup first.
 

Sperber

Active member
#4
@Jake, that query worked flawlessly. But now I have another problem. The value in the field is the area code (5 digits) of that user. Unfortunal, the Google API links them all to the wrong country. To solve that I´ld have to put a country-code before the digits (i.e. US-12345):

IF value of

- table: xf_user_profile
- field_id: xyz

has 5 numeric digits [!only]

INSERT [prepend]

'US-'

Any chance you could help me one more time with that query to finish the task? Would really appreciate that.

Regards,
Sperber
 

Jake Bunce

XenForo moderator
Staff member
#6
@Jake, that query worked flawlessly. But now I have another problem. The value in the field is the area code (5 digits) of that user. Unfortunal, the Google API links them all to the wrong country. To solve that I´ld have to put a country-code before the digits (i.e. US-12345):

IF value of

- table: xf_user_profile
- field_id: xyz

has 5 numeric digits [!only]

INSERT [prepend]

'US-'

Any chance you could help me one more time with that query to finish the task? Would really appreciate that.

Regards,
Sperber
Code:
UPDATE xf_user_profile AS up
SET up.xyz = (
   SELECT IF(ufv.field_value REGEXP '^[[:digit:]]+$', CONCAT('US-', ufv.field_value), ufv.field_value)
   FROM xf_user_field_value AS ufv
   WHERE ufv.user_id = up.user_id
   AND ufv.field_id = 'abc'
)
WHERE up.xyz = '';
 

Sperber

Active member
#7
Code:
UPDATE xf_user_profile AS up
SET up.xyz = (
   SELECT IF(ufv.field_value REGEXP '^[[:digit:]]+$', CONCAT('US-', ufv.field_value), ufv.field_value)
   FROM xf_user_field_value AS ufv
   WHERE ufv.user_id = up.user_id
   AND ufv.field_id = 'abc'
)
WHERE up.xyz = '';
Thank you so much, Jake! One question before I´ll execute the query: does "SELECT IF(ufv.field_value REGEXP '^[[:digit:]]+$', CONCAT('US-', ufv.field_value), ufv.field_value)" include the conditional "where 5 digits (only!)"? We have users from about 5 countries and the others have 4 digit area codes. Would produce a mess, when they become included and altered, too.
 

Jake Bunce

XenForo moderator
Staff member
#8
Code:
UPDATE xf_user_profile AS up
SET up.xyz = (
   SELECT IF(CHAR_LENGTH(ufv.field_value) = 5 AND ufv.field_value REGEXP '^[[:digit:]]+$', CONCAT('US-', ufv.field_value), ufv.field_value)
   FROM xf_user_field_value AS ufv
   WHERE ufv.user_id = up.user_id
   AND ufv.field_id = 'abc'
)
WHERE up.xyz = '';
 

Sperber

Active member
#9
Heck, must have missed your answer,Jake. Just tested the query the other minute but unfortunal it returns 0 hits. Field IDs and tables are named properly and doublechecked. May be you have any idea, why this refuses to work?