UPDATE tbl_sales_d_pabrik set nprice = 5000 WHERE cnofaktur IN(
SELECT tsd.cnofaktur FROM tbl_sales_d_pabrik tsd, tbl_sales_h_pabrik tsh where tsh.cnofaktur=tsd.cnofaktur
AND tsh.ckode_customer='B20DEP00001' AND tsd.ckode_produk='1402010'
AND tsh.dtanggal BETWEEN '2016-04-15' AND '2016-04-30'
);
Jika dijalankan akan terjadi error berikut :
ERROR 1093 (HY000): You can't specify target table 'tbl_sales_d_pabrik' for update in FROM clause
Solusinya :
UPDATE tbl_sales_d_pabrik tsd LEFT JOIN tbl_sales_h_pabrik tsh ON tsh.cnofaktur=tsd.cnofaktur
SET nprice = 5000
WHERE tsh.ckode_customer='B20DEP00001' AND tsd.ckode_produk='1402010'
AND tsh.dtanggal BETWEEN '2016-04-15' AND '2016-04-30'
Referensi
- MySQL Error 1093 - Can't specify target table for update in FROM clause, http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause