CODEWITHSUNDEEP
mysqlsql
Jake
Jake

Reputation: 4234

Insert new row unless ONE of the values matches another row?

I'm trying to insert new rows into a MySQL table, but only if one of the values that I'm inserting isn't in a row that's already in the table.

For example, if I'm doing:

insert into `mytable` (`id`, `name`) values (10, `Fred`)

I want to be able to check to see if any other row in the table already has name = 'Fred'. How can this be done?

Thanks!

EDIT

What I tried (can't post the exact statement, but here's a representation):

INSERT IGNORE INTO mytable (`domain`, `id`) 
VALUES ('i.imgur.com', '12gfa')
WHERE '12gfa' not in (
    select id from mytable
)

which throws the error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE '12gfa' not in ( select id from mytable)' at line 3

Upvotes: 0

Views: 71

Answers (3)

Nik Terentyev
Nik Terentyev

Reputation: 2310

DELIMITER |
CREATE PROCEDURE tbl_update (IN id INT, IN nm VARCHAR(15))
BEGIN
DECLARE exst INT;
SELECT count(name) INTO exst FROM mytable WHERE name = nm;
IF exst = 0 THEN
     INSERT INTO mytable VALUES(id, name);
END IF;
END
|
DELIMITER ;

or just make an attribute name as UNIQUE

Upvotes: 0

Matt Clark
Matt Clark

Reputation: 28639

You could use something like this

INSERT INTO someTable (someField, someOtherField)
VALUES ("someData", "someOtherData")
ON DUPLICATE KEY UPDATE someOtherField=VALUES("betterData");

This will insert a new row, unless a row already exists with a duplicate key, it will update it.

Upvotes: 0

napolux
napolux

Reputation: 16114

First of all, your id field should be an autoincrement, unless it's a foreign key (but I can't assume it from the code you inserted in your question).

In this way you can be sure to have a unique value for id for each row.

If it's not the case, you should create a primary key for the table that includes ALL the fields you don't want to duplicate and use the INSERT IGNORE command.

Here's a good read about what you're trying to achieve.

Upvotes: 1

Related Questions