CODEWITHSUNDEEP
mysqlspl
Torf
Torf

Reputation: 1244

mySQL UPDATE or INSERT or DELETE in one statement

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?

Upvotes: 0

Views: 62

Answers (0)

Related Questions