MySQL : Update multiple tables with a single query (even without correlation)
Updating it one by one wouldn't be effective both in case of code writing and performances.
Fortunately, in MySQL we can update multiple tables with a single query even though both of tables has no relation each other.
For example :
On a block of the program, I want to update the field named 'keluar' and 'saldo akhir' on table named 'barang_mutasi' with a primary key named 'kd_barang' valued 'A0001'.
And I also have to reduce the amount of field named 'stock' on the 'barang_master' table according to the same specified primary key 'kd_barang' ('A0001').
As we seen from the description above, there is no correlation between the two tables, the only similarity is both of them have a field named 'kd_barang' with a same value 'A0001'
So the SQL script would be like this:
UPDATE barang_mutasi a, barang_master b
SET a.keluar = a.keluar + 1, a.saldo_akhir = a.saldo_akhir - 1, a.tgl_update=now(), b.stock = b.stock - 1, b.tgl_update=now()
WHERE a.kd_barang = 'A0001' AND a.yemm=EXTRACT(year_month FROM now()) AND b.kd_barang='A0001';
I give them a different highlight, so it became clear that there was no relation between the two tables
Interesting, isn't it?