mysql query to if exists update else insert query without unique id

Question

I need a mysql query to update data if exists, else insert data without unique id.

Actually i have a table like this

    id  company_ID  Employee_ID Name        Relationship    Dob     Age Gender       
    1   EMPL        00001       Choodamani  Spouse      11-Aug-66   49  Female            
    2   EMPL        00001       Komala      Mother      30-Oct-39   76  Female            
    3   EMPL        00001       Varshini    Daughter    29-Apr-04   11  Female            
    4   EMPL        00001       Vasudevan   Employee    15-Jul-62   53  Male    
    5   EMPL        00002       Siddharth   Son         1-Jun-00    15  Male              
    6   EMPL        00002       Poongavanam Mother      21-Oct-39   76  Female            
    7   EMPL        00002       Aruna       Spouse      16-Sep-68   47  Female            
    8   EMPL        00002       Abirami     Daughter    7-May-97    18  Female            
    9   EMPL        00002       Murali      Employee    7-Oct-67    48  Male

if have insert a data like this,

        id  company_ID  Employee_ID Name        Relationship    Dob     Age Gender       
        1   EMPL        00001       Choodamani  Spouse      11-Aug-70   45  Female            
        2   EMPL        00001       Nirmal      Son      30-Oct-39   76  Female

In above that i have update first employee Dob and Age, In second id 2 you can see am insert new data for same employee

Here what i need is , update a data if exist else insert as new data without unique, I need a query for this

In this table i did'nt have any unique field, id field alone has primary key and auto increment

presently am using this query to insert

INSERT INTO employee (company_id, employee_id, name, relationship, dob, age, gender) VALUES ('$company_id','$employee_id', '$name', '$relationship', '$dob', '$age', '$gender')

is like studying your table how can different people share the same employment id — Gideon Appoh
– Gideon Appoh, Commented Jul 4, 2015 at 5:31
actually this insert has been done while importing excel sheet — Nodemon
– Nodemon, Commented Jul 4, 2015 at 5:34

Barmar · Accepted Answer · 2015-07-04 05:53:17Z

1

Create a unique key on the two columns employee_id, name, and relationship:

CREATE UNIQUE INDEX empid_name ON employee (employee_id, name, relationship);

I added relationship to the key in case an employee has a son with the same name as their father, for example.

Then you can use INSERT INTO employee ... ON DUPLICATE KEY UPDATE ... to either add or update a row depending on whether it already exists.

edited Jul 4, 2015 at 5:53

answered Jul 4, 2015 at 5:45

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

4 Comments

Drew Over a year ago

He all but wrote it for you Ex

Barmar Over a year ago

You already have the INSERT code in the question, it should work as you wrote it.

Drew Over a year ago

Problem is if employees dad is bob and in comes friend bob you just blew away data

Drew Over a year ago

Ok brother or sister who had a sex change

Dave Chen · Accepted Answer · 2015-07-04 05:46:26Z

The only distinguishing factor that you stated in the comments is the name + employee_id. Here's some code demonstrating this:

CREATE TABLE employee (
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
    company_ID varchar(32),
    Employee_ID  INT(11),
    Name varchar(32),
    Relationship varchar(32),
    Dob varchar(32),
    Age INT(11),
    Gender varchar(32),
    UNIQUE KEY employee_id_name (Employee_ID, Name)
);

INSERT INTO employee VALUES

    (1,   'EMPL',        00001,       'Choodamani',  'Spouse',      '11-Aug-66',   49,  'Female'),            
    (2,   'EMPL',        00001,       'Komala',      'Mother',      '30-Oct-39',   76,  'Female'),           
    (3,   'EMPL',        00001,       'Varshini',    'Daughter',    '29-Apr-04',   11,  'Female'),            
    (4,   'EMPL',        00001,       'Vasudevan',   'Employee',    '15-Jul-62',   53,  'Male'),    
    (5,   'EMPL',        00002,       'Siddharth',   'Son',         '1-Jun-00',    15,  'Male'),              
    (6,   'EMPL',        00002,       'Poongavanam', 'Mother',      '21-Oct-39',   76,  'Female'),            
    (7,   'EMPL',        00002,       'Aruna',       'Spouse',      '16-Sep-68',   47,  'Female'),           
    (8,   'EMPL',        00002,       'Abirami',     'Daughter',    '7-May-97',    18,  'Female'),            
    (9,   'EMPL',        00002,       'Murali',      'Employee',    '7-Oct-67',    48,  'Male');


INSERT INTO employee (company_id, employee_id, name, relationship, dob, age, gender) VALUES ('EMPL',        00001,       'Choodamani',  'Spouse',      '11-Aug-70',   45,  'Female')
ON DUPLICATE KEY UPDATE company_id=VALUES(company_id), employee_id=VALUES(employee_id), name=VALUES(name), relationship=VALUES(relationship), dob=VALUES(dob), age=VALUES(age), gender=VALUES(gender);

INSERT INTO employee (company_id, employee_id, name, relationship, dob, age, gender) VALUES ('EMPL',        00001,       'Nirmal',      'Son',      '30-Oct-39',   76,  'Female')
ON DUPLICATE KEY UPDATE company_id=VALUES(company_id), employee_id=VALUES(employee_id), name=VALUES(name), relationship=VALUES(relationship), dob=VALUES(dob), age=VALUES(age), gender=VALUES(gender);

Output

And in PHP use:

INSERT INTO employee (company_id, employee_id, name, relationship, dob, age, gender) VALUES ('$company_id','$employee_id', '$name', '$relationship', '$dob', '$age', '$gender') ON DUPLICATE KEY UPDATE company_id=VALUES(company_id), employee_id=VALUES(employee_id), name=VALUES(name), relationship=VALUES(relationship), dob=VALUES(dob), age=VALUES(age), gender=VALUES(gender)

Collectives™ on Stack Overflow

mysql query to if exists update else insert query without unique id

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related