INSERT WHERE NOT EXIST

Question

I tried following query :

  INSERT INTO `chat_lines` (`channel`, `nick`, `value`)
    VALUES ('Alfa', 'Beta', '0')
    WHERE NOT EXIST
    (
    SELECT * 
    FROM `chat_lines` 
    WHERE `channel` = 'Alfa' AND `nick` = 'Beta'
    );

How can I insert only if record doesn't exist yet?

Channel and nick can't be unique, because one channel can have records with many nicks and one nick can have many channels.

Something like:

(Alfa | Alfa)
(Alfa | Beta)
(Beta | Alfa)
(Beta | Beta).

So, I don't want to have two identical records like

(Alfa|Beta)(Alfa|Beta).

Or maybe there is other way to solve my problem? I just want to create record with specific nick and channel if it doesn't exist, and if it exist, I want to incrase value.

@edit I tried to use REPLACE. It works. But... I want to change value = value + 1. And it is only possible in UPDATE AFAIK. What can I do?

Appears to be a duplicate of https://stackoverflow.com/questions/913841/mysql-conditional-insert — Elk, Mar 05 '15 at 00:03
possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — Dour High Arch, Mar 05 '15 at 00:22
Why can you not add an unique key over channel and nick? `ALTER TABLE chat_lines ADD UNIQUE( channel , nick);` — cSteusloff, Mar 05 '15 at 00:25
@cSteusloff Will I be able to create two records where nick = Alfa if nick will be UNIQUE? ... — n0zz, Mar 05 '15 at 02:17
`UNIQUE( channel , nick)` creates a unique key on the *combination* of these 2 columns, not on *each* of them. Which means you can still have duplicates in `channel`, duplicates in `nick` but no duplicates on the combination of both, which is exactly what you want. — Capsule, Mar 05 '15 at 02:30

score 3 · Accepted Answer · edited May 23 '17 at 11:56

If you want only one value for each pair (nick, channel), first create a unique index on this pair:

CREATE UNIQUE INDEX `chat_lines_by_nick_channel`
ON `chat_lines` (`nick`, `channel`);

This lets you have rows with values ('Staisy', 'nesdev'), ('Milo', 'nesdev'), and ('Milo', 'fireworks') for these fields, just not two separate rows with ('Milo', 'fireworks').

Then you can use ON DUPLICATE KEY UPDATE to have MySQL change value instead of inserting a new row, or INSERT IGNORE INTO chat_lines to leave it unchanged when you try to insert a new row. If you want to add 1 to value, for instance, you can do this:

INSERT INTO chat_lines (`nick`, `channel`, `lines`)
VALUES ('Chester', 'orchard', 1)
ON DUPLICATE KEY UPDATE `value` = `value` + 1

This will add a new row if there is no row matching ('Chester', 'orchard'). Otherwise, it'll add 1 to the value in the existing row.

INSERT WHERE NOT EXIST

2 Answers2