How do I INSERT new row as copy of previous and UPDATE old row value in mysql

Question

I am looking for a simple approach to solve my current challenge. I have the following table:

Id Location DateFrom   DateTo     RateType     Rate 
1  LA       01/01/2017 01/01/2018 AverageRate  18

A few mysql commands in my code behind to select these values and display them to textboxes/datepickers:

public partial class MainWindow : 
{
public MainWindow()
{
    InitializeComponent();
    string connectionString = "datasource= ;port=3306;username= ;password= ";
    MySqlConnection connection = new MySqlConnection(connectionString);
    MySqlCommand sAR = new MySqlCommand("Select Amount from Daily.Rates where RateType = 'Average Hourly Wages'", connection);
    MySqlCommand sARF = new MySqlCommand("Select DateFrom from Daily.Rates where RateType = 'Average Hourly Wages'", connection);
    MySqlCommand sART = new MySqlCommand("Select DateTo from Daily.Rates where RateType = 'AverageRate'", connection);

    try
    {
        connection.Open();
        txtAR.Text = sAHW.ExecuteScalar().ToString();
        dfAR.Text = sAHWF.ExecuteScalar().ToString();
        dtAR.Text = sAHWT.ExecuteScalar().ToString();
    }

    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    connection.Close();
}

What I am trying to achieve:

If I need to increase the rate to 20 I want to keep the old rate of 18, add a copy of that row with the updated values and change the DateTo field as follows: new DateFrom - 1. Basically if I change the rate today I want to end up with this:

Id Location DateFrom   DateTo                 RateType     Rate
1  LA       01/01/2017 10/11/2017(DateFrom-1) AverageRate  18
2  LA       10/12/2017 01/01/2018             AverageRate  20

I have quite a few textxboxes that might not change so if none of the values have changed, I don't want to insert a new row, just keep the old values. What would be the best approach to write this query? How can I efficiently combine an if statement with Insert, Update and Select and what I already have in the textboxes in order to achieve my goal? Thank you.

The row Id for the old row is 1. I am not selecting it although I think it might help. — Cosmin, Oct 12 '17 at 22:22

Jacques Amar · Accepted Answer · 2017-10-13T02:56:49.550

Possible repeat of Select Modify and Insert into the same table

You will need two queries, since there is an INSERT and an UPDATE. To make sure you don't get race conditions, you will have to do it in a TRANSACTION. For this, I suggest a Stored Procedure.

Assuming your input values are DateFrom and NewRate and OldRate and that Id field auto-increments:

delimiter #

CREATE PROCEDURE Keep_Old_Row
(
IN p_DateFrom DATETIME, 
IN p_NewRate INT,
IN p_OldRate INT
)
BEGIN

INSERT INTO Daily.Rates
(Location, DateFrom,   DateTo,     RateType,     Rate  )
SELECT Location, p_DateFrom, DateTo, RateType, p_NewRate 
FROM Daily.Rates
WHERE Rate=p_OldRate;

UPDATE Daily.Rates
SET DateTo=DATE_SUB(p_Date_From, INTERVAL 1 DAY), Rate=p_NewRate
WHERE Rate=p_OldRate;

END#

delimiter ;

HOWEVER, you will need the other fields, so:

INSERT INTO Daily.Rates
(Location, DateFrom,   DateTo,     RateType,     Rate [,... Other Fields]  )
SELECT Location, p_DateFrom, DateTo, RateType, p_NewRate  [,... Other Fields]

is really what you want.

Thank you so much Jacques. Your example is really concise and it helped me a lot. I had to remove Rate=p_NewRate from the Update query in order to keep the old rate the same. The solution works great! Hat off to you! — Cosmin, Oct 13 '17 at 17:59

How do I INSERT new row as copy of previous and UPDATE old row value in mysql

1 Answers1