I have a quite large Excel file (about 40.000 rows) which needs to be inserted in a mySQL DB on a frequent basis. Thanks to Spout I can read the file without hassle, but I got stuck with the SQL-Statement. The Excel looks like this
| id | first_name | last_name |
|---|---|---|
| 23334 | Mark | Spencer |
| 342217 | Gerald | Butler |
and I need to insert IDs that are currently not in my table, update if they are in the table already, and delete them from the table if they are not in the Excel anymore. Due to the massive number of rows I'd like to use as few SQL-Statements as possible so that I do not run in any problems with memory size or script timeout.
Update / Insert where quite easy with
INSERT INTO myTable (ID, FirstName, LastName) VALUES (:id, :firstname, :lastname)
ON DUPLICATE KEY UPDATE
FirstName = VALUES(FirstName),
LastName = VALUES(LastName)
But I cannot find a way to add the delete statement without adding another query. Does anybody know a solution?