You can't specify target table 'barang' for update in FROM clause

Question

I have problem updating column jml_tersedia from table barang. The value should be: barang.jml_tersedia = pembelian.jml_beli - penjualan.jml_jual.

Here is my code:

update barang set jml_tersedia =
(
  ( select ifnull(sum(jml_beli), 0) 
    from pembelian 
    where pembelian.id_barang = (select id_barang 
                                 from barang 
                                 where nama_barang = 'folio')
  ) - 
  ( select ifnull(sum(jml_jual), 0) 
    from penjualan 
    where penjualan.id_barang = (select id_barang 
                                 from barang 
                                 where nama_barang = 'folio')
  ) 
)
where barang.nama_barang = 'folio';

It gave me You can't specify target table 'barang' for update in FROM clause. Can anyone help? Thanks.

Answer 1

MySQL won't let you modify the same table that you're selecting from, but it does let you specify multiple tables in the UPDATE clause.

I've rephrased your UPDATE query to use LEFT JOIN's instead of nested sub-SELECT's.

Since I am not completely sure this query will do exactly what you need, you should first verify that you will get the desired updated value for barang.jml_tersedia. Here it is, re-phrased as a SELECT query:

Verify:

SELECT
    (
        IFNULL(SUM(pb.jml_beli), 0) -
        IFNULL(SUM(pj.jml_jual), 0)
    ) AS new_jml_tersedia
FROM
    barang ba
    LEFT JOIN pembelian pb ON (ba.id_barang = pb.id_barang)
    LEFT JOIN penjualan pj ON (ba.id_barang = pj.id_barang)
WHERE
    ba.nama_barang = 'folio';

Once you're sure that it returns the correct update value, then you can run it, as such:

Update:

UPDATE
    barang ba
    LEFT JOIN pembelian pb ON (ba.id_barang = pb.id_barang)
    LEFT JOIN penjualan pj ON (ba.id_barang = pj.id_barang)
SET
    ba.jml_tersedia = (
        IFNULL(SUM(pb.jml_beli), 0) -
        IFNULL(SUM(pj.jml_jual), 0)
    );
WHERE
    ba.nama_barang = 'folio';

And, if this answer doesn't work out for you, then you could also look at these excellent answers for alternate solutions.

Finally, you can consult MySQL's documentation to learn more about UPDATE Syntax.