Insert values if they don't already exist

Question

INSERT INTO [dbo].[LikesRefined] (userA,userB)                  
SELECT l1.[user],l1.likes
FROM [dbo].[Like] l1
inner join [dbo].[Like] l2 on l2.[user] = l1.likes and l2.likes = l1.[user]
WHERE l1.[user] < l1.likes

I'm inserting values userA,userB if there is a match in [dbo].[Like] into [dbo].[LikesRefined]

How can I only insert records that don't already exist in [dbo].[LikesRefined] ?

`AND NOT EXISTS(SELECT 1 FROM dbo.LikesRefined WHERE userA = l1.[user] AND UserB = l1.likes)` — Lamak, Dec 01 '14 at 15:36

Jt2ouan · Accepted Answer · 2014-12-01T16:10:38.553

Try the merge as well. but performance could be an issue because merge forces you to do matched/unmatched so you'd be updating the records that exist even though its not needed.

 SELECT l1.[user],l1.likes
 INTO #recordsToInsert
 FROM [dbo].[Like] l1
 inner join [dbo].[Like] l2 on l2.[user] = l1.likes and l2.likes = l1.[user]
 WHERE l1.[user] < l1.likes


MERGE LikesRefined AS T
USING #recordsToinsert AS S
ON (T.userA = S.user AND T.userB=s.likes)
WHEN NOT MATCHED BY TARGET
THEN INSERT(userA, userB) VALUES(S.user, S.likes)
WHEN MATCHED BY TARGET THEN
UPDATE T SET userA=user,userB=likes WHERE userA=user and userB=likes;

DROP TABLE #recordsToInsert

score 1 · Answer 2 · edited Dec 03 '14 at 05:19

1

I believe you can just add the NOT EXISTS to your where clause and check the LikesRefined table to determine whether it exists or not first.

INSERT INTO [dbo].[LikesRefined] (userA,userB)                  
SELECT l1.[user],l1.likes
FROM [dbo].[Like] l1
inner join [dbo].[Like] l2 on l2.[user] = l1.likes and l2.likes = l1.[user]
WHERE l1.[user] < l1.likes
AND NOT EXISTS(SELECT 1 
               FROM dbo.LikesRefined 
               WHERE userA = l1.[user] AND UserB = l1.likes)

edited Dec 03 '14 at 05:19

Ardalan Shahgholi

10,041
13
95
124

answered Dec 01 '14 at 15:49

Jt2ouan

1,834
8
27
55

`INSERT INTO [dbo].[LikesRefined] (userA,userB) SELECT l1.[user],l1.likes FROM [dbo].[Like] l1 inner join [dbo].[Like] l2 on l2.[user] = l1.likes and l2.likes = l1.[user] WHERE l1.[user] < l1.likes AND NOT EXISTS( SELECT 1 FROM dbo.LikesRefined WHERE userA = l1.[user] or userA = l1.likes AND userB = l1.[user] or userB = l1.likes )` It's not working, returns 0 rows when I insert new data into the likes table. – user3922757 Dec 01 '14 at 15:55
That NOT EXISTS is not the same as what I have you added code with the OR. – Jt2ouan Dec 01 '14 at 15:59
I have to add in the `or` because when they are inserted userA from the likes table could be inserted into either of the columns of the likesRefined table. So I'm checking if userA is in column A or B and UserB is column A or B . – user3922757 Dec 01 '14 at 16:03

score 0 · Answer 3 · edited Dec 01 '14 at 16:06

0

Try this please

if not exists (select 1 from [dbo].[LikesRefined] where (userA, userB) in
(select l1.[user],l1.likes
FROM [dbo].[Like] l1
inner join [dbo].[Like] l2 on l2.[user] = l1.likes and l2.likes = l1.[user]
WHERE l1.[user] < l1.likes))
begin

--- insert query

end;

edited Dec 01 '14 at 16:06

Tanner

20,318
9
56
73

answered Dec 01 '14 at 15:39

hatem87

135
4

http://i.imgur.com/IkdVjo2.png – user3922757 Dec 01 '14 at 15:43

LCIII · Answer 4 · 2014-12-01T15:57:07.230

Use a left join

INSERT INTO [dbo].[LikesRefined] (userA,userB)

(SELECT l1.[user],l1.likes
FROM [dbo].[Like] l1
LEFT JOIN LikesRefined
ON l2.[user] = l1.likes AND l2.likes = l1.[user]
WHERE l2.user IS NULL)

The select statement will return what is in the Like table where the corresponding record in LikesRefined is null (i.e., non-existant). Just make sure you choose a normally non-nullable field in LikesRefined.

Insert values if they don't already exist

4 Answers4