Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Development » Insert into query
Add to briefcase

Insert into query Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
rabisco
Posted Wednesday, February 23, 2011 10:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 9:49 AM
Points: 72, Visits: 227
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
RP_DBA
Posted Wednesday, February 23, 2011 1:57 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:31 PM
Points: 580, Visits: 814
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
rabisco
Posted Wednesday, February 23, 2011 3:07 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 9:49 AM
Points: 72, Visits: 227
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
rabisco
Posted Wednesday, February 23, 2011 3:33 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 9:49 AM
Points: 72, Visits: 227
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
RP_DBA
Posted Wednesday, February 23, 2011 6:01 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:31 PM
Points: 580, Visits: 814
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
Paul White
Posted Wednesday, February 23, 2011 11:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 10,990, Visits: 10,540 
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
rabisco
Posted Thursday, February 24, 2011 10:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 9:49 AM
Points: 72, Visits: 227
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.