MySQL Forums
Forum List  »  Newbie

INSERT, UPDATE, or DELETE based on a JOIN
Posted by: William Butler
Date: May 15, 2013 08:39AM

I'm tasked with synchronizing data between an IBM mainframe and an application server running MySQL. The mainframe is running a student management system and the application server is running a degree audit system. The only link between the two is by FTP. The mainframe data is considered the MASTER data, and the application server is the SLAVE.

My idea is to extract data from the mainframe, FTP it to the application server, import the data into a fresh table in MySQL, and run a JOIN to determine what's different between the two tables.

To keep it simple, let's say that the student has the following data on the mainframe:
MATH B
ECON C
GEOL A
And the following data on the application server:
MATH C
BIOL D
GEOL A

I want to know if there is a simple way to do a JOIN to determine the differences and issue the approprate INSERT, UPDATE, and DELETE commands.

In my example, the application server table would have an UPDATE on the MATH grade; a DELETE on the BIOL row; an INSERT on the ECON row; and no change on the GEOL row.

I know how to do all of this programmatically using the result of a JOIN statement, but I want to know if MySQL has any built in features to do this more efficiently.

Once the mainframe table data has been applied to the application table, it will be ignored until the next day when I empty it and reload it with the next day's import.

EDIT: I must mention that not everything in the SLAVE file will be found in the MASTER file. There are columns in the SLAVE file that cannot be found on the MASTER. I am only tasked with updating where MASTER and SLAVE overlap one another.



Edited 1 time(s). Last edit at 05/15/2013 08:52AM by William Butler.

Options: ReplyQuote


Subject
Written By
Posted
INSERT, UPDATE, or DELETE based on a JOIN
May 15, 2013 08:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.