Wednesday, 25 May 2016

ERROR 1093 : Can't specify target table for update in FROM clause

SQL :

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

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

Friday, 20 May 2016

sql_mode=only_full_group_by

Status : Draft

Error :

 SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tirtaerp2012.t.ckode_customer' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by. Failing Query: "SELECT t.ckode_customer AS t__ckode_customer, t.cpayment AS t__0, t2.ckode_jenisbayar AS t2__ckode_jenisbayar, t2.cketerangan AS t2__1, t.cpayment AS t__0, t2.cketerangan AS t2__1 FROM tbl_customer t LEFT JOIN tbl_jenisbayar t2 ON t.cpayment = t2.ckode_jenisbayar GROUP BY t.cpayment"

Referensi

  1. MySQL 5.7.5: GROUP BY respects functional dependencies!, http://rpbouman.blogspot.co.id/2014/09/mysql-575-group-by-respects-functional.html
  2. Proposal to enable sql mode ONLY_FULL_GROUP_BY by default, http://www.tocker.ca/2014/01/24/proposal-to-enable-sql-mode-only-full-group-by-by-default.html


Friday, 13 May 2016