CODEWITHSUNDEEP
mysqlsql
user3006586
user3006586

Reputation: 1

copy data from one table, format it to another

I want to copy a string from one table, format it into date format and place it in another table.I have tried this -

$SQL2 = "UPDATE action_data 
SET date_of_call = STR_TO_DATE(date_holding_table.date_1, '%d/%m/%Y') 
FROM date_holding_table WHERE date_holding_table.id = action_data.id";
$result = mysqli_query($dbc, $SQL2);

Which doesnt create any error codes but doesnt copy the data either. I know my source data is ok as I have managed to convert it before using different code. Thanks

Upvotes: 0

Views: 70

Answers (3)

Uours
Uours

Reputation: 2492

Make sure that in date_holding_table table , date_1 column is of '%d/%m/%Y' format else STR_TO_DATE( date_holding_table.date_1 ,'%d/%m/%Y' ) probably will return NULL and nothing gets updated .

STR_TO_DATE

Tested and works :

UPDATE
     action_data
    ,date_holding_table
SET
    action_data.date_of_call = STR_TO_DATE( date_holding_table.date_1 ,'%d/%m/%Y' )
WHERE
    date_holding_table.id = action_data.id

Test data :

-- Table structure for table `date_holding_table`

CREATE TABLE IF NOT EXISTS `date_holding_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_1` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

-- Dumping data for table `date_holding_table`

INSERT INTO `date_holding_table` (`id`, `date_1`) VALUES
(1, '1/5/2013'),
(2, '20/4/2004');

-- Table structure for table `action_data`

CREATE TABLE IF NOT EXISTS `action_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_of_call` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

-- Dumping data for table `action_data`

INSERT INTO `action_data` (`id`, `date_of_call`) VALUES
(1, '0000-00-00 00:00:00'),
(2, '0000-00-00 00:00:00');

After running the my UPDATE query :

2 rows affected.

-- Dumping data for table `action_data`

INSERT INTO `action_data` (`id`, `date_of_call`) VALUES
(1, '2013-05-01 00:00:00'),
(2, '2004-04-20 00:00:00');

Upvotes: 1

peterm
peterm

Reputation: 92805

Your multi-table UPDATE syntax for MySQL is incorrect. Table references should go before SET clause. Try it this way

UPDATE action_data a JOIN date_holding_table d
    ON a.id = d.id
   SET a.date_of_call = STR_TO_DATE(d.date_1, '%d/%m/%Y')

Here is SQLFiddle demo

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

I'm actually surprised you are not getting an error. You didn't specify your database engine, but most of them will support this syntax.

update action_data
set date_of_call = STR_TO_DATE(date_holding_table.date_1, '%d/%m/%Y') 
from action_data join date_holding_table on date_holding_table.id = action_data.id

Upvotes: 0

Related Questions