phpmysqlcodeigniterjoinsql-update

CodeIgniter - MySQL Error 1064 (Update table1 inner join table2(...))

Is now 6am in the morning and i'm still struggling to execute a query with the CodeIgniter PHP framewok. Hope you guys can help me

Code:

$query='
UPDATE `STUDY_LIST_AUX`
INNER JOIN `study_report` 
ON `STUDY_LIST_AUX.study_iuid`=`study_report.study_iuid`
SET `STUDY_LIST_AUX.report_date`=DATE_FORMAT(`study_report.report_date`,\'%Y-%m-%d %h:%i:%s\'), `STUDY_LIST_AUX.report_status` = `study_report.report_status`
';

if ($this->db->query($query))
{
        echo "True!<br><br>";
}
else
{
        echo "False<br><br>";
};

Error:

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE STUDY_LIST_AUX INNER JOIN study_report ON `STUDY_LIST_AUX.study_iu' at line 22

I've tried everything, backticks, normal ticks, quote marks, but the error persists. On phpmyadmin the query run successfully.

Any suggestions or ideas will be much appreciated

Thanks in advance guys :)

Solution

  • You can update your query code using this below Active Record code

    $data = array('s.report_date' => 'DATE_FORMAT(`study_report.report_date`,\'%Y-%m-%d %h:%i:%s\')','s.report_status' => 'sr.report_status');
$this->db->update('STUDY_LIST_AUX s JOIN study_report sr on s.study_iuid = sr.study_iuid',$data);

    This'll help you...