3

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
Adinia
  • 3,722
  • 5
  • 40
  • 58
Mokkun
  • 708
  • 4
  • 14
  • 1
    Think [this](http://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists) might be what you're looking for? – vanamerongen Jul 24 '13 at 08:55
  • [Replace](http://dev.mysql.com/doc/refman/5.0/en/replace.html)? One caveat though, it isn't the same as `MERGE` in Oracle. This either `INSERT`s or `DELETE`s and `INSERT`s. – S.R.I Jul 24 '13 at 08:57
  • The tickets_users.id in auto increment and I don't want to remove the old one if the type, the tickets_id and the users_id matches, just do nothing, only insert if it doesn't match – Mokkun Jul 24 '13 at 09:40
  • Why do not you first all your to be inserted data into a temp table. and then insert into your tickets_users using 'where not exists' conditions. Something like INSERT into tickets_users select * from temptable where temptable.tickets_id not in (select tickets_id from tickets_users) – Krishna Rani Sahoo Jul 24 '13 at 10:31

2 Answers2

4
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)
Akhil
  • 2,602
  • 23
  • 36
  • Thanks your request seems to work but how do I use this request for multiple INSERT? for example with SELECT 455,1444,2 WHERE tickets_id = 455 but also SELECT 425,1226,2 WHERE tickets_id = 425 etc... – Mokkun Jul 24 '13 at 09:30
  • Could you please edit your answers to show me how the query would look with Union all for let say 2 tickets? – Mokkun Jul 24 '13 at 12:11
  • Thanks a lot, will try that, so basicaly I add "union all" between each ticket? – Mokkun Jul 24 '13 at 12:40
  • With my current understanding, yes. But again depend on from where you get these bulk data from. Are you getting this from any table? if so, we can add a join to that table instead of multiple union alls – Akhil Jul 24 '13 at 12:42
  • What do you mean from any table? My request will always be the same: select 456, 1444, 2 from dual WHERE not exists (select 1 from tickets_users where type = 2 and tickets_id = 456) with a variable instead of 456 depending on the ticket I wanna check and also a variable instead of 1444 depending on the user. – Mokkun Jul 24 '13 at 13:59
4

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.

Ganesh Rengarajan
  • 2,006
  • 13
  • 26
  • just tried `INSERT IGNORE INTO tickets_users (tickets_id, users_id, type, use_notification) VALUES (1, 1444, 2, 1)` and it still adds it, I'm doing something wrong? – Mokkun Jul 24 '13 at 09:09
  • r u add key to the column – Ganesh Rengarajan Jul 24 '13 at 09:19
  • I dont think `INSERT IGNORE` is a recommended usage. It ignores all kinds of errors, even though it should have been caught like incorrect format etc. [See this](http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update) – Akhil Jul 24 '13 at 09:24
  • ya please see my note – Ganesh Rengarajan Jul 24 '13 at 09:27
  • I don't want to check the ID since it is auto increment, I just want to check the users_id the tickets_id and the type – Mokkun Jul 24 '13 at 09:42