CODEWITHSUNDEEP
mysql
Abhishek Pandey
Abhishek Pandey

Reputation: 13

How to update data in table if data is match other then insert in mysql

Insert a row in sql if any match then update it.

I tried it 

INSERT INTO test (name, email)
SELECT * FROM (SELECT 'Rupert', '[email protected]') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM test WHERE name = 'Rupert'
) LIMIT 1;

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM `test`;

+----+--------+-----------------+
| id | name   | email           |
+----+--------+-----------------+
|  1 | Rupert | [email protected] | 
+----+--------+-----------------+

This is only work for checking duplicate entry. but i want if no enrty found then insert it or other than update it.

INSERT INTO test (name, email)
SELECT * FROM (SELECT 'Rupert', '[email protected]') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM test WHERE name = 'Rupert'
) LIMIT 1;

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM `test`;

+----+--------+-----------------+
| id | name   | email           |
+----+--------+-----------------+
|  1 | Rupert | [email protected] | 
+----+--------+-----------------+

Upvotes: 1

Views: 609

Answers (2)

Vishal Thakkar
Vishal Thakkar

Reputation: 61

First of all you need to RUN this query 

ALTER TABLE test ADD CONSTRAINT email UNIQUE (email);

After Then use this it will update your record if exist

INSERT INTO test (name,email) VALUES ('Gorilla','[email protected]') ON DUPLICATE KEY UPDATE email = "[email protected]"

Upvotes: 0

yuannet
yuannet

Reputation: 11

You can use ON DUPLICATE KEY

first, you have to create unique index: ALTER TABLE testADD UNIQUE email(email`);

thus, if you insert a record with the same email value, it will update the record. 

INSERT INTO test (name, email) VALUES("Rupert", "[email protected]") 
ON DUPLICATE KEY UPDATE name="Rupert", email="[email protected]"

if it's a new email, it will create a new record

Upvotes: 1

Related Questions