Tuesday, February 28, 2012

MySQL : Update multiple tables with a single query (even without correlation)


Sometimes in programming activities, we encounter a condition in which we need to update some fields in two or more different tables.
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?

1 comment:

  1. Quite interesting and nice topic chosen for the post. I would collect more information about this topic.
    Gateway Laptops

    ReplyDelete

Leave your comments with attitude please. This blog does DOFOLLOW :-)