0

I have a MySql database containing data about users of an application. This application is in production already, however improvements are added every day. The last improvement I've made changed the way data is collected and inserted into the database.

Just to be clearer, my database is composed of 5 tables containing user data and 1 table to relate all the tables, through foreign keys. These 5 foreign keys, together, form my Unique Index for this "Main Table" I have.

The issue is that one of these tables containing user data changed its format, and I want to remove all the data older than the modification I made on my application (just from this table, the other ones I need to keep untouched). However, this dataset has foreign keys in the main table, and I can't just drop these lines on the main table because the other informations I have are important. I tried to change the value of the foreign key for this table, in specific, but then, obviously, I have a problem related to duplicated indexes.

Reading on internet, I've found a solution to my problem using "Insert ... On duplicate key update ...", but i'm not inserting data, just updating it. I have an Idea about how to make a program on PHP to update my database, but is there another easier solution? Is it possible to avoid these problems using just MySql syntax?

João Bruno Abou Hatem de Liz
  • 633
  • 10
  • 22

1 Answers1

0

might be worth looking at the below link

http://www.kavoir.com/2009/05/mysql-insert-if-doesnt-exist-otherwise-update-the-existing-row.html

user2015144
  • 555
  • 2
  • 4
  • 13
  • I had the "ON DUPLICATE KEY UPDATE" in mind, but thought that there were another alternatives in MySql syntax (since I didn't want to make a select on database, get all rows I want to re-insert with another foreign keys, and insert it again using this routine). I was searching for something more "atomic". But, thanks a lot, I solved my problem with "INSERT .. On duplicate key update" – João Bruno Abou Hatem de Liz May 17 '13 at 21:28