[MyBatis] Update Join - table끼리 join해서 update 하기

내가 사용한 방법

• 환경
  • java 8
  • mybatis
  • postgreSQL

<update id="updateJoinAdjmCompleted" parameterType="String">
    /* SmartroPay.xml updateJoinAdjmCompleted */
    UPDATE tb_mbr_pay SET
        mpay_sadj_id = temp.padj_sadj_id
        , mpay_sadj_stat = #{mpayAdjmStat}
        , mpay_adjm_dttm = temp.adj_date
        , mpay_updt_dttm = now()
    FROM (
        SELECT a.*, c.adj_date 
        FROM tmp_mpay_adjm a
        LEFT JOIN tb_shp_adjm b
        ON a.padj_sadj_id = b.sadj_id
        LEFT JOIN tb_adjm c
        ON b.sadj_adjm_id = c.adj_id
        where a.tid not in (select tid from tmp_mpay_adjm where state_cd != '0')
    ) as temp
    WHERE tb_mbr_pay.mpay_id = temp.padj_mpay_id
</update>