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.
Austin Wright.
---------------------------------------------------
PLUG-discuss mailing list -
PLUG-discuss@lists.plug.phoenix.az.us
To subscribe, unsubscribe, or to change your mail settings:
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
(text/plain)