CODEWITHSUNDEEP
mysqlinsertconditional-statements
Mokkun
Mokkun

Reputation: 726

INSERT if the value doesn't already exists

In my database, I have tickets_users that affiliate users to a ticket.

I'd like to do an INSERT that checks in tickets_users if there is not already this users_id (for example user 1444) and type = 2 WHERE tickets_id = something (for example ticket number 455); if it already exists do nothing, else 

INSERT INTO tickets_users (tickets_id, users_id, type) VALUES (455, 1444, 2) 
WHERE tickets_id = 455

Upvotes: 3

Views: 7924

Answers (2)

Akhil
Akhil

Reputation: 2602

INSERT INTO tickets_users (tickets_id, users_id, type) 
select 455, 1444, 2 from  dual
WHERE not exists (select 1 from tickets_users where type = 2 and tickets_id = something)

Try this

Edit :
To add multiple rows

INSERT INTO tickets_users (tickets_id, users_id, type) 
select 455, 1444, 2 from  dual
WHERE not exists (select 1 from tickets_users where type = 2 and tickets_id = 455)
union all 
select 456, 1444, 2 from  dual
WHERE not exists (select 1 from tickets_users where type = 2 and tickets_id = 456)

Upvotes: 4

Ganesh Rengarajan
Ganesh Rengarajan

Reputation: 2006

You can use a PRIMARY KEY or UNIQUE Index on a table with appropriate fields to stop duplicate records.

PRIMARY KEY

Use INSERT IGNORE rather than INSERT. If a record doesn't duplicate an existing record, MySQL inserts it as usual. If the record is a duplicate, the IGNORE keyword tells MySQL to discard it silently without generating an error.

INSERT IGNORE

For Example: the id is assumed to be the primary key. If a record in the table 'tablename' has already a record with id = 1 then the query will just be ignored instead of producing a warning.

Note: Use the IGNORE keyword only during the development phase. It could cause unexpected results as you cannot identify if the record has been inserted or not.

Upvotes: 4

Related Questions