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

score 2 · Accepted Answer · answered Jun 01 '21 at 14:53

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”

answered Jun 01 '21 at 14:53

LambdaTheDev

160
1
9

how can I add new rows as the update first before deleting the old data then? – anarchy Jun 01 '21 at 14:57
First add the new ID column: ALTER TABLE datatable ADD id INT, then mark it as primary key: ALTER TABLE datatable ADD PRIMARY KEY id – LambdaTheDev Jun 01 '21 at 15:00
I highly suggest you also reading more about ALTER TABLE statement, Google it ;) – LambdaTheDev Jun 01 '21 at 15:01
please take a look – anarchy Jun 01 '21 at 15:07
Maybe just delete those rows, without update. If you want to update all data from specific date to new date, just do it like this - UPDATE datatable SET date=“new-date” WHERE date=“old-date”. After you have updated rows to new date, execute command you provided in question - it is good – LambdaTheDev Jun 01 '21 at 15:10
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/233183/discussion-between-anarchy-and-lambdathedev). – anarchy Jun 01 '21 at 15:24

score 0 · Answer 2 · answered Jun 01 '21 at 14:55

0

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.

answered Jun 01 '21 at 14:55

O. Jones

103,626
17
118
172

can i do a composite ? i thought it has to be unique – anarchy Jun 01 '21 at 14:58
https://stackoverflow.com/questions/67792069/how-and-why-to-add-primary-keys-to-my-sql-database-table-when-i-already-have-an could you take a look at this question? – anarchy Jun 01 '21 at 15:49

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

2 Answers2