XF 1.5 SQL: Copying field value with conditional

[Sperber]

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]

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]

Thanks, Jake !

 

[Sperber]

@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

 

[Sperber]

Anyone? Help appreciated

 

[Jake Bunce]

@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

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]

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]

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]

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?