php - 更新两个表 Codeigniter

Question

我正在尝试同时更新 2 个表 tbl_loanledger 和 tbl_journal。两个表都有相同的列，例如 modified_by、date_modified 和 deleted。

当我尝试运行它时，我在此处输入代码，但出现错误:

Error Number: 1052

Column 'modified_by' in field list is ambiguous

UPDATE `loan_ledger` as a, journal as b SET `modified_by` = '1', `date_modified` = '2016-06-08', `deleted` = 1 WHERE `a`.`id` = '823' AND `b`.`id` = '823'

Filename: C:\xampp\htdocs\system\system\database\DB_driver.php

Line Number: 331

这是关于如何在 codeigniter 中更新 2 个表的正确方法吗？

型号

public function delete($id){

  $data = array(
        'modified_by' => $this->ion_auth->user()->row()->id,
        'date_modified' => date("Y-m-d"),
        'deleted' => 1
      );

    $this->db->set($data);
    $this->db->where('a.id', $id);
    $this->db->where('b.id', $id);
    $this->db->update('loan_ledger as a, journal as b');

}

但如果我只是更新单个表，它就可以工作，这是代码:

public function delete($id){

  $data = array(
        'modified_by' => $this->ion_auth->user()->row()->id,
        'date_modified' => date("Y-m-d"),
        'deleted' => 1
      );
    $this->db->where('id', $id);
    $this->db->update(tbl_loanledger, $data);

}

Answer 1

不使用连接只写两个单独的更新查询

 // first 
    $this->db->set('a.modified_by', $this->ion_auth->user()->row()->id);
    $this->db->set('a.date_modified', date("Y-m-d"));
    $this->db->set('a.deleted', 1);
    $this->db->where('a.id',  $id);
    $this->db->update('loan_ledger as a');
    // second
    $this->db->set('b.modified_by', $this->ion_auth->user()->row()->id);
    $this->db->set('b.date_modified', date("Y-m-d"));
    $this->db->set('b.deleted', 1);
    $this->db->where('b.id',  $id);
    $this->db->update('journal as b');