Not sure if this'll work or not (base table script and sample data would be helpful) but try adding userID and another set of parenthesis around the ORs in your NOT EXISTS logic

insert into userprofile(userID, fieldID, value,)

select userID, 1000, 'external'

from user

where not exists (

selectuserID

fromuserprofile

whereuserid = user.userid

andfieldID = '1000'

and(user.email LIKE '%@hotmail%' or

user.email LIKE '%@gmail%' or

user.email LIKE '%@googlemail%' or

user.email LIKE '%@yahoo%'))