[SOLVED] MySQL a little help

[arespi]

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?

[atreyu]

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:

#!/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.

[arespi]

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?

[atreyu]

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.

[Kloschüssel]

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:

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

[arespi]

Thanks a million Kloshüssel,that did the trick!. Just had to change the %y for %Y because it need a 4 digit year.