Update a column within a selection of data in SQL table and save it

Question

I have the following SQL table called datatable.

date         source    reading    tday  yday
---------------------------------------------
2021-05-27   x1        x          x     x   
2021-05-27   x2        x          x     x  
2021-05-27   x3        x          x     x  
2021-05-28   x1        x          x     x   
2021-05-28   x2        x          x     x  
2021-05-28   x3        x          x     x  
2021-05-31   x1        x          x     x   
2021-05-31   x2        x          x     x  
2021-05-31   x3        x          x     x  

I want to update the date from 2021-05-31 to 2021-06-01, I only have 1 database and I'm worried about messing up the data, how can I do it?

It should look like this

date         source    reading    tday  yday
----------------------------------------------
2021-05-27   x1        x          x     x   
2021-05-27   x2        x          x     x  
2021-05-27   x3        x          x     x  
2021-05-28   x1        x          x     x   
2021-05-28   x2        x          x     x  
2021-05-28   x3        x          x     x  
2021-06-01   x1        x          x     x   
2021-06-01   x2        x          x     x  
2021-06-01   x3        x          x     x  

I think it would be something like

UPDATE date 
FROM datatable 
WHERE date = '2021-05-31' TO '2021-06-01';

or

UPDATE datatable 
SET date = '2021-06-01' 
WHERE date = '2201-05-31';

The date column is a date time format.

EDIT.

How can I add the data first, then delete the old one?

So like, copy the data from 2021-05-31 as 2021-06-01, (all the other columns would be columns from 2021-05-31's data except the date)

date         source    reading    tday  yday
---------------------------------------------
2021-05-27   x1        x          x     x   
2021-05-27   x2        x          x     x  
2021-05-27   x3        x          x     x  
2021-05-28   x1        x          x     x   
2021-05-28   x2        x          x     x  
2021-05-28   x3        x          x     x  
2021-05-31   x1        x          x     x   
2021-05-31   x2        x          x     x  
2021-05-31   x3        x          x     x  
2021-06-01   x1        x          x     x   
2021-06-01   x2        x          x     x  
2021-06-01   x3        x          x     x  

Then delete the rows where 2021-05-31, so I end up with

For this I'm guessing the query would be:

DELETE FROM datatable 
WHERE date = '2021-05-31';

date         source    reading    tday  yday
--------------------------------------------
2021-05-27   x1        x          x     x   
2021-05-27   x2        x          x     x  
2021-05-27   x3        x          x     x  
2021-05-28   x1        x          x     x   
2021-05-28   x2        x          x     x  
2021-05-28   x3        x          x     x  
2021-06-01   x1        x          x     x   
2021-06-01   x2        x          x     x  
2021-06-01   x3        x          x     x  

Answer 1

Your second sample UPDATE does what you want.

But you are correct that such an UPDATE can be destructive. Many systems with data like yours don't UPDATE their tables when they have a new day's data. Instead, they INSERT new rows into the table, so the table holds the history.

If you're concerned out how big your tables will get, don't be. Databases are good at handling large tables, especially the kind you showed us. And you could always delete data that's more than a few years old in some kind of maintenance job.

If I understand your data, you could use a composite primary key made from your date and source columns.

Answer 2

For the safest result, do it as the Adminer database solution do for tables without primary key:

UPDATE datatable SET date=“new-date” WHERE date=“old-date” AND source=“previous-source” AND reading=“previous-reading” AND the rest of fields.

But, to make your life easier, I suggest adding the id column, (AUTO_INCREATMENT & PRIMARY_KEY), and update data using:

UPDATE your-update-query…. WHERE id=“target-id”