CODEWITHSUNDEEP
phpmysqldatabasedatabase-design
TGuimond
TGuimond

Reputation: 5487

MySQL Alter Table using existing field values

I am working with a database that has a table called date, which contains a separate field for day, month, year. Clearly this is not ideal when I am trying to run comparisons, etc. I am wondering is it possible for me to add a DateTime field to each row of this table and insert a concatenated string into the new field from the existing day, month, year fields.

I am quite sure its possible, I'm just wondering if anyone might be able to point me in the right direction on how to achieve this?

Below is the current date table: (i know)

CREATE TABLE IF NOT EXISTS `date` (
  `deposition_id` varchar(11) NOT NULL default '',
  `day` int(2) default NULL,
  `month` int(2) default NULL,
  `year` int(4) default NULL,
  PRIMARY KEY  (`deposition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Upvotes: 1

Views: 860

Answers (3)

Alpesh
Alpesh

Reputation: 5405

Try this (untested) - 

UPDATE date d SET d.datetime = (SELECT CONCAT('-','year','month','day') from date d1 where d1.id = d.id);

Upvotes: 1

user319198
user319198

Reputation:

first use alter table query:

alter table date add column datetimefield datetime NOT Null

then

use update query with self join on date and update datetimefield with concat on date,month, year column values.

Upvotes: 2

Vladislav Rastrusny
Vladislav Rastrusny

Reputation: 29985

What is the problem, I don't understand? Alter the table, add new DATE column and then populate it with a string "yyyy-mm-dd" using CONCAT mysql function or whatever.

Upvotes: 0

Related Questions