INSERT SELECT query with DUPLICATE KEY UPDATE

mysql_batch_insert.sql

#	title           : mysql_batch_insert.sql
#	description     : INSERT SELECT query with DUPLICATE KEY UPDATE
# author          : Agung Pambudi
# website         : http://agungpambudi.com
#	email           : mail@agungpambudi.com
# requirement 	  : MySQL / Maria DB
# alternative     : REPLACE query in MySQL
# 			                         _         _ _
#  ___ ___ _ _ ___ ___ ___ ___ _____| |_ _ _ _| |_|  ___ ___ _____
# | .'| . | | |   | . | . | .'|     | . | | | . | |_|  _| . |     |
# |__,|_  |___|_|_|_  |  _|__,|_|_|_|___|___|___|_|_|___|___|_|_|_|
#     |___|       |___|_|
#

# first query
INSERT INTO `marginloss_$random` (`division`,`item_code`,`desc`,`net`,`hpp`,`margin`,`margin_percent`) SELECT a.`division`, a.kd_brg AS `item_code`, b.desc AS `desc`, SUM(a.t_sales - a.t_retur - a.tppn) AS net, SUM(a.rp_sales - a.rp_retur) AS hpp, (SUM(a.t_sales - a.t_retur - a.tppn)-SUM(a.rp_sales - a.rp_retur)) AS margin, (SUM(a.t_sales - a.t_retur - tppn) - SUM(a.rp_sales - a.rp_retur)) / SUM(a.t_sales - a.t_retur - a.tppn) * 100 AS margin_percent FROM gilet092019 AS a LEFT JOIN masterproduct092019 AS b ON a.kd_brg = b.kd_brg WHERE a.toko='R00L' GROUP BY a.kd_brg ON DUPLICATE KEY UPDATE net = VALUES(net), hpp = VALUES(hpp), margin = VALUES(margin), margin_percent = VALUES(margin_percent);

# other query
INSERT INTO table_name (id, name, age) VALUES (3, "Agus", 21), (4, "Budi", 19), (5, "Hatori", 25) ON DUPLICATE KEY UPDATE name = VALUES(name), ....

# alternative
REPLACE INTO _jadwal (tanggal, nama, shift) VALUES ('2019-08-09', AES_ENCRYPT('FIKO', 'key12345'), AES_ENCRYPT('1', 'key12345'));