MySQL bulk INSERT or UPDATE

mysql sql bulk
rid picture rid · Jun 9, 2011 · Viewed 59.1k times · Source

Is there any way of performing in bulk a query like INSERT OR UPDATE on the MySQL server?

INSERT IGNORE ...

won't work, because if the field already exists, it will simply ignore it and not insert anything.

REPLACE ...

won't work, because if the field already exists, it will first DELETE it and then INSERT it again, rather than updating it.

INSERT ... ON DUPLICATE KEY UPDATE

will work, but it can't be used in bulk.

So I'd like to know if there's any command like INSERT ... ON DUPLICATE KEY UPDATE that can be issued in bulk (more than one row at the same time).

Answer

user359996 picture user359996 · Aug 9, 2011

You can insert/update multiple rows using INSERT ... ON DUPLICATE KEY UPDATE. The documentation has the following example:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

Or am I misunderstanding your question?

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More