Avatar of Isaiah Melendez
Isaiah Melendez

asked on 

MySQL Replace Into Question

Hello, Experts,

I am curious about the REPLACE INTO statement in MySQL. I am currently using it as follows and I want to make sure I am using it correctly.

I have a historical table with 3 million-plus records. Then I have a staging table that will pull data down from an API daily and then run a replace statement into the historical table from the new data that came in. The new data coming in will be as follows:

  • New data that does not exist in the historical table
  • data existing in the historical but fields that have been changed (i.e: first name, total sales amount, etc)

What I expect to happen:

  • new data that does not exist will be inserted into the historical table
  • data that exists it will compare the primary key and update the rows that are in the staging table to the data in the historical

Is my assumption correct that my update process is working as expected?

The only thing that made me question the process is I made a change on the API level to ingest data that I thought was coming through and exposing it but it wasn't. It now is. When I checked the staging and historical tables this morning I would have expected the data to be updated for the fields that were missing data and do not see it updated.

My primary key is called repair_order_id and the records I am pulling down are based on a date called updaed_date which will show the latest records that are new or updated.

* syntaxMySQL ServerSQL

Avatar of undefined
Last Comment
Isaiah Melendez

8/22/2022 - Mon