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

Find users without custom field set


Well-known member

I've got a custom field called "nrep_status", which is set for 90% of our users (to either 'Valid' or 'Invalid'). However, I'd like to change this so that the 10% of non-assigned users have this custom field value set and updated to 'Invalid'.

Initially, I bulk assigned all of our users via the following query, but due to a problem with the script that assigns the value during registration, some members have no custom field set:

INSERT INTO xf_user_field_value (user_id, field_id, field_value)
SELECT xf_user.user_id, 'nrep_status', 'Valid'
FROM xf_user
LEFT JOIN xf_user_option ON (xf_user.user_id = xf_user_option.user_id)
WHERE user_state = 'valid' AND xf_user.is_banned = 0
field_value = VALUES(field_value);

Is there a way to add a field for the unassigned users that meet the where clause criteria, without touching the status of those that have already been set? I just can't get my head around the MySQL for this.

Basically, I want to run the above query, but only where there is no existing "field_value" set for the user.