Search code examples
mysqlsqlxmlload-data-infile

Load XML Update Table--MySQL

 LOAD XML LOCAL INFILE 'file1.xml'
 INTO TABLE my_table
 ROWS IDENTIFIED BY '<product>'"

Is it possible to use this function to update a table?

I used REPLACE INTO TABLE my_table but that only added new rows and it did not update the existing rows.

Solution 
  •  LOAD XML LOCAL INFILE 'file1.xml'
 REPLACE 
 INTO TABLE my_table
 ROWS IDENTIFIED BY '<product>'"

    See: http://dev.mysql.com/doc/refman/5.5/en/load-xml.html

    Note that:

    REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 12.2.5, “INSERT Syntax”.

    Probably MySQL is unable to delete the exiting rows due to foreign key restrictions.

    You can fix this by:

    SET FOREIGN_KEY_CHECKS=0;
...load xml
SET FOREIGN_KEY_CHECKS=1;