Re: Mysql updating dates in a table

Top Page
Attachments:
Message as email
+ (text/plain)
+ (text/html)
+ (text/plain)
Delete this message
Reply to this message
Author: JD Austin
Date:  
To: Main PLUG discussion list
Subject: Re: Mysql updating dates in a table
If the current column was a date type there had to be a ton of failures!
If you have unique values in the data you can just craft update statements
from the csv file. Use excel/OO Calc, perl, etc to parse apart the original
file to create update table statements that you can pipe back to mysql after
you test a few.

--
JD Austin
Twin Geckos Technology Services LLC

480.288.8195x201
http://www.twingeckos.com


Jonathan Swift<http://www.brainyquote.com/quotes/authors/j/jonathan_swift.html>
- "May you live every day of your life."

On Tue, Aug 4, 2009 at 4:26 PM, Craig White <> wrote:

> On Tue, 2009-08-04 at 16:21 -0700, JD Austin wrote:
> > http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
> > You can use Str_to_date to convert it to a date.
> > Date/timestamp data should be one of the date types (Datetime, date,
> > timestamp).
> > I'd do it like this (all sql and won't lose data):
> >      1. Add a column to the table that is date/datetime/timestamp (in
> >         your case date looks right)
> >      2. update the new column using the str_to_date function
> >      3. Check for rows that have nulls in that column - fix them
> >      4. set the existing column value to NULL
> >      5. change the column to a date using alter table.
> >      6. update the column to be whatever you called the new column you
> >         added.
> >      7. Drop the new column that you added.
> >      8. add not null constraint to your original date column
> ----
> Yes, I am on dev.mysql.com... (v5.0 is correct too)

>
> OK, well they imported as dates...just wrong dates, completely wrong
> dates.
>
> I have done 'set' the contents of one field from another field before I
> think...that was easy.
>
> So how would I import records with the same primary key and just add a
> new date field to the existing records with matching primary key?
>
> Craig
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
> ---------------------------------------------------
> PLUG-discuss mailing list -
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>

---------------------------------------------------
PLUG-discuss mailing list -
To subscribe, unsubscribe, or to change your mail settings:
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss