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.
---------------------------------------------------
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)