Using MySQL temporary table

1. Create temporary table with the settlements table structure:

CREATE TEMPORARY TABLE sett LIKE settlements;

2. Insert into the temporary table the data from the settlements for a certain date:

INSERT INTO sett (date,symbol,contract,price)
SELECT date,symbol,contract,price
FROM pltracker.settlements
WHERE date = '2010-09-03';

2a. You can do the previous two steps in only one:

CREATE TEMPORARY TABLE sett
SELECT date,symbol,contract,price
FROM settlements
WHERE date = '2010-09-03';

3. Update the date of the data at the temporary table for the specific date of the settlements:

UPDATE sett
SET date = '2010-09-06'
WHERE date = '2010-09-03';

3a. Since you are updating all the data in the table you can use this SQL statement instead:

UPDATE sett
SET date = '2010-09-06';

4. Insert data back into the settlements table:

INSERT INTO settlements (date,symbol,contract,price)
SELECT *
FROM sett;

5. Check that the new data is in place at the settlements table:

SELECT * FROM settlements
WHERE date = '2010-09-06';

6. The temporary table will be automatically drop when you close the session or you can drop it as any other table:

DROP TABLE sett;