Update date ranges table using only a date column-CodePudding

I have a table to save data according to date ranges. For example, in the example table, the ranges would go from 07 to 9, the next from 10 to 14...

How to do in sql if a user wants to modify a date range?

ID    start_date         data_id       price
===    =======           =======      =======
 1    2022-10-07            3            10
 2    2022-10-10            5            50
 3    2022-10-15            5            20
 4    2022-10-16            3            40
       ...                ...          ...

For example, if user want to update range from 2022-10-08 to 2022-10-12 with price 30, the table should be as follows:

ID    start_date         data_id       price
===    =======           =======      =======
 1    2022-10-07            3            10
 5    2022-10-08            3            30
 2    2022-10-13            5            50
 3    2022-10-15            5            20
 4    2022-10-16            3            40
       ...                ...          ...

I can only think of complicated ways to do this.

EDIT FOR MORE INFO:

mysql version: 5.7.38
Data_id, in this example it has no use, we can ignore it for simplicity

CREATE TABLE calendar ( ID bigInt, start_date DATE, data_id int, price int );

The UPDATE query is really the one that I can't find how to do it. The user simply provides a start and end date, and a price. Each range will have a different price from the previous one, otherwise it would be included in it, as shown in the example.

CodePudding user response：

CREATE PROCEDURE update_calendar (date_from DATE, date_till DATE, new_price INT)
BEGIN
    IF NOT EXISTS ( SELECT NULL
                    FROM calendar
                    WHERE start_date = date_till   INTERVAL 1 DAY ) THEN
        INSERT INTO calendar (start_date, data_id, price)
        SELECT date_till   INTERVAL 1 DAY, data_id, price
        FROM calendar
        WHERE start_date <= date_till
        ORDER BY start_date DESC LIMIT 1;
    END IF;
    DELETE 
    FROM calendar 
    WHERE start_date BETWEEN date_from AND date_till;
    INSERT INTO calendar (start_date, price) 
    SELECT date_from, new_price;
END

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=6de2801efc9ab2ae7627ac86d323cc09

I recommend you to perform the whole operations as solid transaction. I.e. define a falg variable, create according handler, add START TRANSACTION, and add COMMIT/ROLLBACK depends on the flag which is set in the handler while any problem occures during the data edition.

for example I update from 10 to 13 with a price of 100, the line for day 10 would not have to be created, since day 9 already has the correct price

CREATE PROCEDURE update_calendar (date_from DATE, date_till DATE, new_price INT)
BEGIN
    IF NOT EXISTS ( SELECT NULL
                    FROM calendar
                    WHERE start_date = date_till   INTERVAL 1 DAY ) THEN
        INSERT INTO calendar (start_date, data_id, price)
        SELECT date_till   INTERVAL 1 DAY, data_id, price
        FROM calendar
        WHERE start_date <= date_till
        ORDER BY start_date DESC LIMIT 1;
    END IF;
    DELETE 
    FROM calendar 
    WHERE start_date BETWEEN date_from AND date_till;
    INSERT INTO calendar (start_date, price) 
    SELECT date_from, new_price
    WHERE new_price <> COALESCE( ( SELECT price
                                   FROM calendar
                                   WHERE start_date < date_from 
                                   ORDER BY start_date DESC LIMIT 1 ), 0);
END

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=8685671a8d179c4da694c4333801a2a6