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;
