CODEWITHSUNDEEP
mysqlsql-update
Tyler
Tyler

Reputation: 67

How to edit all the data in an entire column?

In my MySQL database, I have a lot of data with the timestamp in this format:

2017.07.13 - 12:00:00:000

I want to change it to be in this format:

2017-07-13T12:00:00:000-0400

I know I need SELECT to get the data from the table and UPDATE to change it to a new record. But I don't know how to edit the timestamp using SQL commands.

How can I edit a string using SQL commands?

Upvotes: 0

Views: 970

Answers (2)

Noor A Shuvo
Noor A Shuvo

Reputation: 2807

Should work (tested in SQL-Server)

Update tableName SET col = Replace(col," - ","T") + '-0400'

If + is not work for mysql then try CONCAT

Update: Solution for MySQL is

UPDATE tableName SET col = CONCAT(REPLACE(REPLACE(col, ' - ', 'T'), '.', '-'), '-0400')

Upvotes: 0

chris85
chris85

Reputation: 23882

You don't need to run a select prior to an update. The update has the data already available. You could do something like this:

update table 
set column = concat(replace(replace(column, ' - ', 'T'), '.', '-'), '-0400')

to alter the format of your all dates in the column column of the table table.

Demo: http://sqlfiddle.com/#!9/2699e9/2 (using select because the update wouldnt show anything)

  1. https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_concat
  2. https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_replace
  3. https://dev.mysql.com/doc/refman/5.7/en/update.html

If you only want to update 1 specific row use a where clause to limit the update affects.

Upvotes: 1

Related Questions