Find the answer to your Linux question:
Results 1 to 6 of 6
Hi, my knowledge of mysql is limited to 'SELECT * FROM TABLE; ' I have this table with a field (pur_date) as date and i need to copy it to ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Linux Newbie arespi's Avatar
    Join Date
    May 2011
    Location
    Monterrey , Mexico
    Posts
    147

    MySQL a little help


    Hi, my knowledge of mysql is limited to 'SELECT * FROM TABLE; '

    I have this table with a field (pur_date) as date and i need to copy it to another field in the same table (field4) as varchar(10) with the format 'mm/dd/yyyy' somebody could help me?

  2. #2
    Trusted Penguin
    Join Date
    May 2011
    Posts
    4,353
    Hey,

    If you mean how to do that in one mysql command, I'm not sure. But if you can do it in two separate calls, via the shell, then you can try something like this:
    Code:
    #!/bin/bash
    
    # define connection parameters
    USERNAME=your_db_username
    PASSWORD=your_db_password
    DATABASE=your_db_name
    DB_TABLE=your_db_table
    
    # mysql command to reuse
    mysql_cmd="mysql -sN -u $USERNAME -p$PASSWORD $DATABASE"
    
    # run command to get the date
    date=$($mysql_cmd -e 'SELECT pur_date FROM $DB_TABLE WHERE field1="value"')
    echo date: $date
    # date will look something like '2012-12-13'
    
    # now separate month, day, and year
    MM=$(echo $date|cut -f2 -d-)
    DD=$(echo $date|cut -f3 -d-)
    YY=$(echo $date|cut -f1 -d-)
    
    # put them together in desired format (MM/DD/YYYY)
    newdate="${MM}/${DD}/${YY}"
    echo date: $date
    
    # run mysql command to insert the new date value into the db
    $mysql_cmd -e "UPDATE $DB_TABLE SET field4='$newdate' WHERE field1='value'"
    Note: the part of the mysql commands WHERE field1='value' can be left off, it is just a place-holder for you to insert your own way of uniquely identifying the record.

  3. #3
    Linux Newbie arespi's Avatar
    Join Date
    May 2011
    Location
    Monterrey , Mexico
    Posts
    147
    Quote Originally Posted by atreyu View Post
    Hey,

    If you mean how to do that in one mysql command, I'm not sure. But if you can do it in two separate calls, via the shell, then you can try something like this:
    .
    Thanks

    And how it would be to run it for the whole table and not just a certain record?

  4. #4
    Trusted Penguin
    Join Date
    May 2011
    Posts
    4,353
    Quote Originally Posted by arespi View Post
    And how it would be to run it for the whole table and not just a certain record?
    oh, if you were doing the whole table then I guess you could just leave off the "WHERE field=value" bit.

  5. #5
    Linux Engineer Kloschüssel's Avatar
    Join Date
    Oct 2005
    Location
    Italy
    Posts
    773
    Be careful: if setting the new date was a per-row action (i.e. a different value for every row) the above script wouldn't work. The following SQL (or a slightly modified version of it) could instead do the job:

    Code:
    UPDATE table SET field=DATE_FORMAT(datefield,'%m/%d/%y')
    The update command is executed for every row matched. Since there is no WHERE it executes on every row and passes the value of datefield into DATE_FORMAT and writes the outcome into the field column of the same row.

    RTFM: MySQL :: MySQL 5.5 Reference Manual :: 12.7 Date and Time Functions

  6. #6
    Linux Newbie arespi's Avatar
    Join Date
    May 2011
    Location
    Monterrey , Mexico
    Posts
    147
    Thanks a million Kloshüssel,that did the trick!. Just had to change the %y for %Y because it need a 4 digit year.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •