Advertisement
Advertisement


Update column issue tsql


Question

Take a look at this

Task is to sum 'ukupna bilansna i vanbilansna aktiva' for the same 'ID_Grupe' after the relation between TABLE_A and TABLE_B on 'mbr' and 'GRP_ID' has been made.

(mbr and GRP_ID are clients id card numbers)

Afterwards, we need to update summed values in TABLE_C in the column 'IDV_REL_P_ESR_AMT' after joining on the same 'ID_IDENTN_NUM'.

So you can think of ID_IDENTN_NUM,mbr and GRP_ID as same id card number.

How can I do joins between three tables, along with an UPDATE and SET statements?

This is my idea:

UPDATE B2_INPT_CR_IP_SNPST AS c
SET IDV_REL_P_ESR_AMT = (SELECT ID_Grupe, SUM(ukupna_bilansna_i_vanbilansna_aktiva)
                         FROM rm_tb_portfolio AS a
                         INNER JOIN B2_INPT_CR_GRP_SNPST AS b ON a.mbr = b.GRP_ID
                         GROUP BY ID_Grupe)
 WHERE c.IP_IDENTN_NUM = a.mbr
2017/12/28
1
2
12/28/2017 7:05:02 AM

Accepted Answer

Try this:

WITH CTE
AS
(
    SELECT ID_Grupe,
           sum(ukupna_bilansna_i_vanbilansna_aktiva) AS Total
    FROM rm_tb_portfolio as a
    INNER JOIN B2_INPT_CR_GRP_SNPST as b ON a.mbr = b.GRP_ID
    GROUP BY ID_Grupe
)
UPDATE c
SET c.IDV_REL_P_ESR_AMT  = c2.Total
FROM B2_INPT_CR_IP_SNPST as c1
INNER JOIN CTE AS c2 ON c1.ID_Grupe = c2.ID_Grupe;

If you want to do insert, you can do it in the same way:

WITH CTE
AS
(
    ....
)
INSERT INTO B2_INPT_CR_IP_SNPST(...)
SELECT ... FROM CTE;

But, you cannot use both INSERT and UPDATE in the same sql statement. However, you can use MERGE in this case, to update existing rows or insert new values.

2017/12/27
4
12/27/2017 11:39:22 AM