1

I have a MySQL table that updates from an API daily, but now I want to track a specific field that has been updated from the API. I want to update the row with the current field(value) change and keep the old field(value) in another field. Could somebody please point me in the right direction and what would be the best practice for this?

Table (original)

---------------------------------
|ID | Input Date | Expired Date |
---------------------------------
| 2 | 2017-05-17 | 2017-12-19   |   
---------------------------------
| 3 | 2017-07-09 | 2018-05-19   |
---------------------------------

Table when updated (current)

---------------------------------
|ID | Input Date | Expired Date |
---------------------------------
| 2 | 2017-05-17 | 2018-05-28   |  <----- ID 2: Expired Date updated
---------------------------------
| 3 | 2017-07-09 | 2018-05-19   |
---------------------------------

Table updated and keeps old field value 

------------------------------------------------
|ID | Input Date | Expired Date | Old Exp Date |
------------------------------------------------
| 2 | 2017-05-17 | 2018-05-28   | 2017-12-19   |  
------------------------------------------------    
| 3 | 2017-07-09 | 2018-05-19   |              | 
------------------------------------------------

I hope this made sense a little, please help..Thank you so much:)

Edward
  • 77
  • 6

3 Answers3

3

You may create a trigger for it

CREATE TRIGGER date_updated BEFORE UPDATE ON <your table name>
       FOR EACH ROW SET <old exp date column> = OLD.<expired date column>

In PostgreSQL, you can use the RETURNING clause

UPDATE <table name> SET <column> = <value> RETURNING <column>
Alexandre Alencar
  • 162
  • 5
1

I found out 2 ways of doing this:

A. Using 2 queries to the database
- update the Old Exp Date with the Expired date value from table
- update the Expired date with the new value from the API

B. Using a single query
- you can try to set both fields at once but you should test it
- it is like SET Old Exp Date = Expired date, Expired date = API_VALUE

Some implementations don't have the same UPDATE order:
sql-update-order-of-evaluation

You should test it to see how it works on your system. If it works then you should decide if there is any risk of portability issues.

Kind Regards,
Lin

Cata Lin
  • 11
  • 2
0

You can simply try the mysql query similar like this,

UPDATE your_table_name 
SET `Old Exp Date` = `Expired Date`, 
`Expired Date` = "$api_value";
V-T
  • 756
  • 2
  • 9
  • 22