CODEWITHSUNDEEP
sqlsqlite
Joakim Engstrom
Joakim Engstrom

Reputation: 6403

Only Insert Into a table if a value is unique on a non-unique row

I'm trying this sqlite query on an existing database and table:

INSERT INTO Actions (guide_id, user_id, action_type, action_date, article_id,  guide_name) VALUES (33, 1199180, 2, 1355829894, 2457, 'Amsterdam');

This works, but if I run the same thing again it will insert duplicate values which is not wanted.

This is how the table looks like:

A description of the Table

The problem here is that article_id is not unique. I want the insert only to run when article_id does not exists in the column.

In theory I want something like:

IF x DOES NOT EXISTS IN article_id DO (INSERT INTO Actions (guide_id, user_id, action_type, action_date, article_id,  guide_name) VALUES (33, 1199180, 2, 1355829894, 2457, 'Amsterdam'));

Is this possible?

Upvotes: 1

Views: 1940

Answers (4)

Robin Kanters
Robin Kanters

Reputation: 5146

Why can't you add a UNIQUE constraint? 

ALTER TABLE Actions
ADD UNIQUE (article_id);

Upvotes: -1

missemisa
missemisa

Reputation: 2688

Maybe something like this?

CREATE TRIGGER delete_duplicates_articles
AFTER INSERT ON Article
BEGIN
   DELETE FROM Article 
   WHERE action_date NOT IN (SELECT MIN(action_date) 
                             FROM Article 
                             WHERE article_id = new.article_id)
   AND article_id = new.article_id;
END;

Upvotes: 1

Kuf
Kuf

Reputation: 17828

try:

INSERT INTO Actions (guide_id, user_id, action_type, action_date, article_id,  guide_name)
VALUES (33, 1199180, 2, 1355829894, 2457, 'Amsterdam') WHERE NOT article_id = x;

Another way would be to define the article_id as a UNIQUE key:

create unique index unique_name on Actions (article_id );

You can find more information at the sqlite manual.

Upvotes: 2

Philipp
Philipp

Reputation: 69663

When you want article_id to be unique, then article_id should be the primary key, not action_id.

Upvotes: 0

Related Questions