php - 更新另一个表中的表 where 条件

Question

我有 2 个名为 po_head 和 po_detail 的表。

po_head 存储有关PO Status = OPEN(剩余数量 > 0)/CLOSED(剩余数量 = 0) 的信息

po_detail 存储有关订购的商品数量和订购的库存的信息。
它们都与 po_invoice 有关...

我需要将 po_header 中的 po_status 更新为 CLOSED IF qty still in po_detail = 0

注意:
剩余数量 = 来自 po_detail pd 的 sum(po_detail_qty)，其中 pd.po_invoice = ph.po_invoice

在 Controller 中创建一些函数后，如下所示:

    function receive(){
    $data       = array();
    $subtotal = 0;
    $total = 0;
    $idnr       = $this->input->post('idtut');
    $id_detail  = $this->input->post('prDetailKodeBarang');
    $merk       = $this->input->post('prDetailMerkId');
    $kategori   = $this->input->post('prDetailKategoriId');
    $qty_receive= $this->input->post('prDetailQtyRcv');
    $price      = $this->input->post('prDetailPrice');
    $podeid     = $this->input->post('poDetailId');
    for($i = 0; $i < count($merk); $i++){
        $subtotal = $qty_receive[$i] * $price[$i];
        $data[] = array(
                        'pr_po_detail_id'       =>  $podeid[$i],
                        'pr_kode_barang'        =>  $id_detail[$i],
                        'pr_merk_item_id'       =>  $merk[$i],
                        'pr_invoice'            =>  $this->input->post('prheadInvoice'),
                        'pr_kategori_item_id'   =>  $kategori[$i],
                        'pr_detail_qty_receive' =>  $qty_receive[$i],
                        'pr_detail_price'       =>  $price[$i],
                    );
        $total += $subtotal;

        $update['po_detail_qty'] = $this->model_po->getKurangStok($podeid[$i],$qty_receive[$i]);
        $key['po_detail_id'] = $podeid[$i];
        $this->model_po->updateData('po_detail',$update,$key);
    }
    $this->db->insert_batch('pr_detail', $data);
    $head = array(
                'pr_invoice'        => $this->input->post('prheadInvoice'),
                'pr_po_invoice'     => $this->input->post('idtut'),
                'pr_pengirim'       => $this->input->post('prPengirim'),
                'pr_total_bayar'    => $total,
                'pr_date'           => $this->input->post('prHeadDate'),
                'pr_receiver'       => $this->input->post('prHeadCreator'),
                'pr_status_payment' => $this->input->post('prStatusPayment')
                );
    $this->model_po->create('pr_head', $head);
    $this->model_po->updateStatusPO($idnr);
    redirect('po');
}

这是我更新状态 PO 的模型:

    function updateStatusPO($idnr){
    $query = (" UPDATE po_head ph SET ph.po_status_po = 'CLOSED' WHERE
                (SELECT SUM( pd.po_detail_qty ) FROM po_detail pd WHERE pd.po_invoice =  '".$idnr."') = '0'
                AND ph.po_invoice =  '".$idnr."'
            ");
}

但是当 po_detail_qty 为“0”时，状态 PO 没有改变。

Answer 1

我已经使用了更新和连接。请尝试这个:

UPDATE po_head ph 
inner join po_detail pd on pd.po_invoice = ph.po_invoice
SET ph.po_status_po = 'CLOSED' WHERE 
                SUM( pd.po_detail_qty ) = 0
                AND ph.po_invoice =  '".$idnr."'