CODEWITHSUNDEEP
mysql
n0zz
n0zz

Reputation: 327

INSERT WHERE NOT EXIST

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?

Upvotes: 1

Views: 119

Answers (2)

Rick James
Rick James

Reputation: 142208

Simply do

INSERT IGNORE INTO `chat_lines` (`channel`, `nick`, `value`)
    VALUES ('Alfa', 'Beta', '0');

See also INSERT...ON DUPLICATE KEY UPDATE... (not relevant, but similar).

Upvotes: 1

Damian Yerrick
Damian Yerrick

Reputation: 4664

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.

See also:

Upvotes: 3

Related Questions