CODEWITHSUNDEEP
mysql
Yax
Yax

Reputation: 2189

INSERT into a table with condition not working in MySQL

I have a table with data on it, student record. I am already using the table with some added data but the record is like half of the total. Now I have been given the complete record and I want to add it to my existing table. I want the INSERT to exclude the records that are already existing in my table to avoid duplication but the entire record of the new table get inserted into the my own table, creating duplicates.

INSERT INTO `studentTable`(`studentID`, `surname`, `first_name`, `middle_name`, 
`department`, `room_number`) 
   SELECT `studentID`, `surname`, `first_name`, `middle_name`, `department`,
   `room_number` 
       FROM newTable WHERE EXISTS 
    (SELECT * FROM studentTable WHERE studentTable.studentID <> newTable.studentID);

Note: I can easily delete the old record and insert the new record but that will be to painful as the existing record is already in use with some added data.

Any help will be appreciated. Deeply.

Upvotes: 0

Views: 58

Answers (1)

Janick Bernet
Janick Bernet

Reputation: 21184

Assuming there's a primary key on studentID (if not, create one), just do INSERT IGNORE and mysql will do the filtering for you. 

INSERT IGNORE INTO `studentTable`
    (`studentID`, `surname`, `first_name`, `middle_name`, `department`, `room_number`)
SELECT
    `studentID`, `surname`, `first_name`, `middle_name`, `department`, `room_number` 
FROM `newTable`;

Upvotes: 1

Related Questions