February 23, 2011 at 10:28 am
I have 2 tables;
user and userprofile
The user table has one row per user which has the following(among others) columns userID, email.
The userprofile table has the following columns userID, fieldID, value
The userprofile table has multiple rows per user, however each row is unique i.e.
for user 001 - row 1 - 001, 1000, external
row 2 001, 1001, US
I am attepmting to update the userprofile table with a new row for each user with the following based on the email field in the user table.....
insert into userprofile(userID, fieldID, value,)
select userID, 1000, 'external'
from user
where not exists (select userID from userprofile where fieldID = '1000'
and user.email LIKE '%@hotmail%' or
user.email LIKE '%@gmail%' or
user.email LIKE '%@googlemail%' or
user.email LIKE '%@yahoo%')
However, duplicate rows are being inserted. If I remove this...
user.email LIKE '%@hotmail%' or
user.email LIKE '%@gmail%' or
user.email LIKE '%@googlemail%' or
user.email LIKE '%@yahoo%')
no duplicate rows are inserted.
My goal is to ensure that user who do not have the row for external, get one once only.
Thanks.
February 23, 2011 at 1:57 pm
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%'))
_____________________________________________________________________
- Nate
February 23, 2011 at 3:07 pm
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.
February 23, 2011 at 3:33 pm
I tried this...
if exists(select email from user where
email LIKE '%@hotmail%' or
email LIKE '%@gmail%' or
email LIKE '%@googlemail%' or
email LIKE '%@yahoo%')
insert into userprofile(userID, fieldID, value, primaryVal, levelID)
select userID, 5001, 'external', 0, NULL
from user ju
where not exists (select userID from userprofile where userID = ju.userID
and fieldID = '5001')
Although the script does not create a duplicate row, it update every other user in the table i.e. with an email address like @bbc.co.uk.
February 23, 2011 at 6:01 pm
How about if you moved the email criteria to the outer select and added a DISTINCT?
insert into userprofile(userID, fieldID, value,)
select distinct userID, 1000, 'external'
from user
where not exists (
select userID
from userprofile
where userid = user.userid
and fieldID = '1000')
and (user.email LIKE '%@hotmail%' or
user.email LIKE '%@gmail%' or
user.email LIKE '%@googlemail%' or
user.email LIKE '%@yahoo%')
_____________________________________________________________________
- Nate
February 23, 2011 at 11:17 pm
CREATE TABLE #User (userID INT PRIMARY KEY, email VARCHAR(100) NOT NULL);
CREATE TABLE #UserProfile (userID INT NOT NULL, fieldID INT NOT NULL, value VARCHAR(50) NOT NULL, PRIMARY KEY (userID, fieldID));
INSERT #User (userID, email) VALUES (1, 'x@hotmail.com');
INSERT #User (userID, email) VALUES (2, 'y@gmail.com');
INSERT #User (userID, email) VALUES (3, 'z@yahoo.com');
INSERT #UserProfile (userID, fieldID, value) VALUES (1, 1000, 'external');
INSERT #UserProfile (userID, fieldID, value) VALUES (1, 1001, 'something');
INSERT #UserProfile (userID, fieldID, value) VALUES (2, 1100, 'blah');
INSERT #UserProfile (userID, fieldID, value) VALUES (2, 1156, 'fish');
INSERT #UserProfile (userID, fieldID, value) VALUES (3, 900, 'spanner');
INSERT #UserProfile (userID, fieldID, value) VALUES (3, 1000, 'external');
INSERT #UserProfile (userID, fieldID, value)
SELECT Missing.userID, 1000, 'external'
FROM (
SELECT U.userID
FROM #User AS U
EXCEPT
SELECT P.userID
FROM #UserProfile AS P
WHERE P.fieldID = 1000
) AS Missing;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 24, 2011 at 10:14 am
RP_DBA (2/23/2011)
How about if you moved the email criteria to the outer select and added a DISTINCT?
insert into userprofile(userID, fieldID, value,)
select distinct userID, 1000, 'external'
from user
where not exists (
select userID
from userprofile
where userid = user.userid
and fieldID = '1000')
and (user.email LIKE '%@hotmail%' or
user.email LIKE '%@gmail%' or
user.email LIKE '%@googlemail%' or
user.email LIKE '%@yahoo%')
Thanks Nate. Just what I was looking for.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply