Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert into query Expand / Collapse
Author
Message
Posted Wednesday, February 23, 2011 10:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 24, 2013 7:56 PM
Points: 76, Visits: 232
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.

Post #1068410
Posted Wednesday, February 23, 2011 1:57 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:13 AM
Points: 620, Visits: 864
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 (
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

@nate_hughes
Post #1068537
Posted Wednesday, February 23, 2011 3:07 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 24, 2013 7:56 PM
Points: 76, Visits: 232
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 (
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 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.
Post #1068572
Posted Wednesday, February 23, 2011 3:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 24, 2013 7:56 PM
Points: 76, Visits: 232
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.
Post #1068585
Posted Wednesday, February 23, 2011 6:01 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:13 AM
Points: 620, Visits: 864
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

@nate_hughes
Post #1068617
Posted Wednesday, February 23, 2011 11:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1068674
Posted Thursday, February 24, 2011 10:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 24, 2013 7:56 PM
Points: 76, Visits: 232
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.
Post #1069089
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse