Update MySQL table if non-primary key column is duplicate, insert otherwise?

Question

I have a MySQL table with an autoincremented primary key, and a column for a person's name, and some data.

Is there a MySQL query where I can insert a new row if the name of the person is unique, but if it's a duplicate name, then I would update that row?

I.e.

ID, Name,        Data
1 , Michael,     x
2 , Stephen,     y
3 , Christopher, z

If I were to add a "Michael" to the database with "qq" data, I would want the database to look like this:

ID, Name,        Data
1 , Michael,     qq
2 , Stephen,     y
3 , Christopher, z

If I were to add "John" with "zz" data, the database would look like this:

ID, Name,        Data
1 , Michael,     x
2 , Stephen,     y
3 , Christopher, z
4 , John,        zz

I know of the command, ON DUPLICATE KEY UPDATE, but I only want to update if the name is the same, not if the primary key is the same.

had you heard about stored procedures ? they make this very simple mysqltutorial.org/mysql-stored-procedure-tutorial.aspx — Manoj Purohit
– Manoj Purohit, Commented Jan 26, 2013 at 18:46

Ignacio Vazquez-Abrams · Accepted Answer · 2013-01-26 18:44:22Z

7

Create a UNIQUE index on Name. ON DUPLICATE KEY UPDATE will then work correctly.

answered Jan 26, 2013 at 18:44

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Manoj Purohit · Accepted Answer · 2013-01-26 18:57:00Z

0

Create a stored procedure and simply call it in your query code

PROCEDURE:

   DROP PROCEDURE IF EXISTS InsUpdTable $$
        CREATE PROCEDURE InsUpdTable
        (
          IN _name VARCHAR(45),
          IN _data VARCHAR(45)
        )
        BEGIN
        if exists (select ID from mytable where name = _name)
        Then
          update mytable set data = _data where name=_name;
        else
          insert into mytable(name, data) values(_name, _data);
        END IF;
        END $$
        DELIMITER ;

Query:

call InsUpdTable('John','zzz')

answered Jan 26, 2013 at 18:57

Manoj Purohit

3,4631 gold badge17 silver badges17 bronze badges

Collectives™ on Stack Overflow

Update MySQL table if non-primary key column is duplicate, insert otherwise?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related