CODEWITHSUNDEEP
mysql
Jaylen
Jaylen

Reputation: 40279

How to insert values from a table only if they do not exist already using mysql?

I am trying to read value from a table them insert it into another table only if the staring that I am trying to insert does not already exist in the table.

I have tried to use the ON DUPLICATE KEY clause but I get a syntax issue that i am unable to fix.

this is my query

SELECT Field1 FROM RSF
INSERT INTO result_codes(result_code_title, created_by)
VALUES (Field1, '2')
ON DUPLICATE KEY UPDATE result_code_title = Field1;

Upvotes: 1

Views: 239

Answers (2)

hjpotter92
hjpotter92

Reputation: 80629

The clause is INSERT INTO ... SELECT and not SELECT ... INSERT INTO. Therefore:

INSERT INTO result_codes( result_code_title, created_by )
SELECT Field1, '2'
FROM RSF
ON DUPLICATE KEY 
    UPDATE result_code_title = Field1;

I think that should work.

Since you only want to insert only if the string doesn't already exist; you should be better off using INSERT INGORE:

INSERT IGNORE INTO result_codes( result_code_title, created_by )
SELECT Field1, '2'
FROM RSF

Upvotes: 2

fancyPants
fancyPants

Reputation: 51868

Use INSERT IGNORE instead.

INSERT IGNORE INTO result_codes(result_code_title, created_by)
SELECT Field1, '2'
FROM RSF

It detects via a primary key or unique index, if a row already exists and doesn't attempt to insert if this is the case.

Upvotes: 1

Related Questions