-1

I would like to write a SQL statement that inserts a new row into the database if there isn't already a row for it. The unique identifier of a row is the id and url. Let's say the table schema looks like this:

LinkClicks: (id, url, clicks)

So now let's say I've got a row with a parameterized SQL insert. I'm attempting to do something like this:

INSERT (id, url, clicks) 
INTO LinkClicks Values(@id, @url, @clicks) 
WHERE @url NOT IN 
    (SELECT url FROM LinkClicks WHERE id=@id);
Captain Stack
  • 3,572
  • 5
  • 31
  • 56
  • 1
    Did you already make a research? Take a look at conditioned insert into: [https://stackoverflow.com/questions/16636698/do-conditional-insert-with-sql](https://stackoverflow.com/questions/16636698/do-conditional-insert-with-sql) – Teneko Oct 25 '17 at 02:14
  • Are you using MySQL or SQL Server? – Tim Biegeleisen Oct 25 '17 at 02:49

3 Answers3

1

I think you want something like this:

INSERT INTO LinkClicks(id, url, clicks)
    SELECT id, url, clicks
    FROM (SELECT @id as id, @url as url, @clicks as clicks) t
    WHERE t.url NOT IN (SELECT url FROM LinkClicks WHERE id = @id);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You can add a unique index on the id and url columns:

ALTER TABLE LinkClicks ADD UNIQUE u_idx (id, url);

With this constraint in place, attempts to insert a record whose id and url combination of values already appears will fail at the database level.

This might be preferable to the query you are attempting, because it guarantees that MySQL will reject a duplicate attempt to insert. A query could also be used to this effect, but later on perhaps someone else using your code base might forget this.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

In fact you should take Tim's advice and put the unique index on the table but in doing so you need a fail safe way of ensuring that you don't attempt to put duplicates (id and url) into the table (otherwise loads of red-ink messages). This way seems ok:

DROP TABLE LINKCLICKS
DROP TABLE LINKCLICKS1

CREATE TABLE LINKCLICKS
(
[ID] INT,
[URL] CHAR(11),
CLICKS BIGINT
)
GO
INSERT INTO LINKCLICKS VALUES (1001,'www.abc.com',40000)
INSERT INTO LINKCLICKS VALUES (1002,'www.def.com',40000)
INSERT INTO LINKCLICKS VALUES (1003,'www.ghi.com',40000)
GO

CREATE TABLE LINKCLICKS1
(
[ID] INT,
[URL] CHAR(11),
CLICKS BIGINT
)
GO
INSERT INTO LINKCLICKS1 VALUES (1001,'www.abc.com',40000)
INSERT INTO LINKCLICKS1 VALUES (1003,'www.def.com',40000)
INSERT INTO LINKCLICKS1 VALUES (1004,'www.ghi.com',40000)
GO

WITH CTE1 AS
(
SELECT *,'d' AS [Source] FROM LINKCLICKS
UNION ALL
SELECT *,'s' AS [Source] FROM LINKCLICKS1
)
,
CTE2 AS
(
SELECT ID,[URL] FROM CTE1 GROUP BY ID,[URL] HAVING COUNT(ID) =1 AND COUNT([URL]) =1
)
INSERT INTO LINKCLICKS
SELECT ID,[URL],CLICKS 
FROM CTE1 
WHERE [Source] <> 'd' 
AND 
(
ID IN (SELECT ID FROM CTE2) AND [URL] IN (SELECT [URL] FROM CTE2)
)

SELECT * FROM LINKCLICKS ORDER BY [ID],URL
GO

The INSERT statement only inserts those rows where the ID and URL combined are not the same as rows already in the destination table. It quite happily inserts rows where either the IDs are the same but the URLs are different or where the IDs are different but the URLs are the same.

My only reservation is the question of 'dupes' in the source table (in this case LINKCLICKS1). If there are duplicates in the source table, none of them will be inserted into the destination table. This will defeat the object of the query.

The answer is, if you have duplicates, or any risk of duplication in the source table, then you should apply 'de-dupe code' to the source table before you run this.

If you need any de-dupe code, put a comment below.

russ
  • 579
  • 3
  • 7