0

I would like to update all records in a MySQL table by first truncating the table and then importing new data from a file.

The issue I am encountering is that I have defined foreign key constraints on the primary key of the table I would like to update, which (rightfully) prevents me from truncating the table and inserting new (updated) data.

The source file for the update I would like to perform contains both updates to existing records and new records to be inserted, so it would not be sufficient to insert only the new records.

I am 100% confident that the primary keys in the existing table and the primary keys in the file match and that the number and type of attributes, as well as all entity constraints, are satisfied. Due to system limitations, it is not sufficient to update the database table; I must export from one system and import into another.

My question is this: is there a way to either truncate and import within a single transaction in order to maintain a consistent state or use phpMyAdmin to import updates and new records, leaving the current data intact?

If it is helpful to know, I am using phpMyAdmin 4.3.8.

Matthew Fisher
  • 27
  • 9
  • 1
    While your question is not exactly the same, the solution in the duplicate question will solve your problem. – Barmar Jun 06 '16 at 20:38
  • @Barmar Great, thank you. Do you know of a way to import the file into phpMyAdmin from my local machine? I cannot store the file on the server. – Matthew Fisher Jun 06 '16 at 20:47
  • I don't use phpMyAdmin, but I'm pretty sure it has an import operation in the menu. – Barmar Jun 06 '16 at 20:48
  • Also, you can use `LOAD DATA LOCAL INFILE` and it will read the file from the client, not the server. – Barmar Jun 06 '16 at 20:48
  • @Barmar It does, but I cannot view the temporary table. I can always create a real table for the same purpose and drop it afterward. – Matthew Fisher Jun 06 '16 at 20:49
  • @Barmar `LOAD DATA LOCAL INFILE` seems to be what I would need. I am having trouble getting it to find the file, but I will check elsewhere for a solution to that problem. Thank you for your guidance. – Matthew Fisher Jun 06 '16 at 20:59

0 Answers0