Mysql updating dates in a table

[Craig White]

On Tue, 2009-08-04 at 16:05 -0700, Austin William Wright wrote:
> Craig White wrote:
> > I need to update almost all (but not quite all) records in a data table
> > because the dates were imported like '08/04/09' instead of '2009-08-04'
> >
> > I am looking at the mysql reference manual but it doesn't appear that it
> > would be easy to script a line by line 'update' from a csv file.
> >
> > Now I still have the csv files (and a copy with the dates fixed) and I
> > suppose I could 'find', 'delete' and then 'import' again but there are
> > relational records so mysql would probably prevent me from doing that
> > unless I suspend all relational checks when I delete those records prior
> > to reimporting them with the fixed dates.
> >
> > What's the best way to accomplish this?
> >
> > Craig
> If there are no records from before 2000, and they are MM/DD/YY, back up
> the table first, then try:
> 
> UPDATE `tablename` SET
> `columnname`=CONCAT("20",SUBSTRING_INDEX(SUBSTRING_INDEX(`columnname`,"/",-2),"/",-1),"-",SUBSTRING_INDEX(`columnname`,"/",1),"-",SUBSTRING_INDEX(SUBSTRING_INDEX(`columnname`,"/",-2),"/",1))
> 
> then
> 
> |ALTER TABLE |`tablename`| CHANGE |`columnname`| |`columnname`| DATE NOT
> NULL |
> 
> Replace all instances of tablename and columnname with the targets, and
> change DATE NOT NULL as necessary. That should work.
----
thanks but I have the dates fixed in the CSV file.

Your command doesn't list a file name to read so I can't see how that
could work at all.

The dates looked right in the CSV file "2009-08-04" for example but OOo
actually saved that sample as 08/04/2009 so when imported, the month,
date and year were all wrong. So I've got a repaired CSV file with the
dates saved in the correct form.

So I either have to run an command that updates the records from a file
matching the primary key with the first column in the CSV file and then
replacing specific date fields with specific columns or just delete the
records and re-import, which seems easiest if I can delete records while
related indexes are ignored.

Craig


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.