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.
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.