gufran gufran - 2 months ago 5
SQL Question

how to update table with sum from another table?

I have 3 tables data_kabupaten, master_data, and kampus

data_kabupaten
+--------------+----------+----------+---------+
|kota/kabupaten|perbulan |j_penduduk|rata-rata|
+--------------+----------+----------+---------+
|makassar |2016-06-31| | |
|makassar |2016-07-31| | |
|makassar |2016-08-31| | |
|... |... | | |
+--------------+----------+----------+---------+

master data kampus
+--------------+-------+ +---------+-------+
|kota/kabupaten|id_kota| |nim |kota_id|
+--------------+-------+ +---------+-------+
|makassar |m012 | |238011003|m012 |
|makassar |m012 | |... |... |
|makassar |m012 | +---------+-------+
|... |... |
+--------------+-------+


how to update my data_kabupaten tables with


j_penduduk = count(distinct[nim])

Answer

Use the below query.

    UPDATE kp
       SET kp.J_penduduk=t.cntNim
     FROM data_kabupaten kp
      JOIN master_data md 
        on kp.[kota/kabupaten] = md.[kota/kabupaten]
       JOIN (SELECT kota_id,Count( distinct Nim) CntNim
                  FROM Kampus k
                  GROUP BY kota_id)t
            On md.id_kota= t.kota_id
Comments