CODEWITHSUNDEEP
mysqlsqlxmlload-data-infile
Ryan
Ryan

Reputation: 14649

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.

Upvotes: 2

Views: 1849

Answers (1)

Johan
Johan

Reputation: 76567

 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;

Upvotes: 1

Related Questions