RP_DBA (2/23/2011)
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%'))
Thanks Hughes, that worked for those 4 email domains, however, new rows were created for other email domains. The goal of this script is to insert a new row for each user i.e. internal, external, and supplier depending on the users email domain.