none
Iterate with master table

    질문

  • Hi,
             Here config_master is a master table .
    i want to update in tran_detl table based configuration available on swt_status,icpn_status,host_status column from config_master.
    Tran_detl table is transaction table it grows with large volume.

    create table config_master
    (
    id int,
    swt_status varchar(20),
    icpn_status varchar(20),
    host_status varchar(20),
    status varchar(20),
    action_desc varchar(200)
    )

    insert into config_master values(1,'S','S','S','Success','Settled')
    insert into config_master values(2,'S','F','S','Failed','Pending')
    insert into config_master values(3,'F','F','S','Failed','Not settled')
    insert into config_master values(3,'F','F','F','Failed','Declined')
    insert into config_master values(3,'S','S','F','Failed','No action')

    create table tran_detl
    (
    tran_id int,
    tran_swt_status varchar(20),
    tran_icpn_status varchar(20),
    tran_host_status varchar(20),
    tran_status varchar(20),
    tran_action_desc varchar(200)
    )



    insert into tran_detl(tran_id,tran_swt_status,tran_icpn_status,tran_host_status) values(10000001,'S','S','S')
    insert into tran_detl(tran_id,tran_swt_status,tran_icpn_status,tran_host_status) values(10000002,'S','S','F')
    insert into tran_detl(tran_id,tran_swt_status,tran_icpn_status,tran_host_status) values(10000003,'S','S','S')
    insert into tran_detl(tran_id,tran_swt_status,tran_icpn_status,tran_host_status) values(10000004,'S','F','S')
    insert into tran_detl(tran_id,tran_swt_status,tran_icpn_status,tran_host_status) values(10000005,'F','F','S')

    Expected result

    Select * from tran_detl

    tran_id         tran_swt_status    tran_icpn_status   tran_host_status    tran_status        tran_action_desc
    10000001        S                    S                 S                             Success  Settled
    10000002        S                                        S                 F                            Failed No action
    10000003        S     S                 S                          Success  Settled
    10000004        S                                        F                 S   Failed Pending

    10000005         F                                        F                 S                          Failed Not settled

    Please help

    2018년 5월 18일 금요일 오전 6:15

답변

  • since what you ask for is an update logic

    what you need is a simple update like below

    update t
    set tran_status = c.status,
    tran_action_desc = c.action_desc
    from tran_detl t
    join config_master c
    on c.swt_status = t.tran_swt_status
    and c.icpn_status = t.tran_icpn_status
    and c.host_status = t.tran_host_status


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • 편집됨 Visakh16MVP 2018년 5월 18일 금요일 오전 7:35
    • 답변으로 표시됨 Antonioy 2018년 5월 18일 금요일 오전 9:16
    2018년 5월 18일 금요일 오전 7:35

모든 응답

  • Hi,

    If tran_detl is a log table without updates you can use a trigger as

    create trigger tran_detl_addupd on tran_detl
    
    instead of insert as
    
    	insert into tran_detl
    
    	select i.tran_id, i.tran_swt_status, i.tran_icpn_status, i.tran_host_status, c.status, c.action_desc 
    
    	from inserted i
    
    	left outer join config_master c
    
    	on c.host_status=i.tran_host_status and c.icpn_status = i.tran_icpn_status and c.swt_status=i.tran_swt_status
    
    go
    

    marc.

    2018년 5월 18일 금요일 오전 7:12
  • since what you ask for is an update logic

    what you need is a simple update like below

    update t
    set tran_status = c.status,
    tran_action_desc = c.action_desc
    from tran_detl t
    join config_master c
    on c.swt_status = t.tran_swt_status
    and c.icpn_status = t.tran_icpn_status
    and c.host_status = t.tran_host_status


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • 편집됨 Visakh16MVP 2018년 5월 18일 금요일 오전 7:35
    • 답변으로 표시됨 Antonioy 2018년 5월 18일 금요일 오전 9:16
    2018년 5월 18일 금요일 오전 7:35
  • create table config_master
    (
    id int,
    swt_status varchar(20),
    icpn_status varchar(20),
    host_status varchar(20),
    status varchar(20),
    action_desc varchar(200)
    )
    
    insert into config_master values(1,'S','S','S','Success','Settled')
    insert into config_master values(2,'S','F','S','Failed','Pending')
    insert into config_master values(3,'F','F','S','Failed','Not settled')
    insert into config_master values(3,'F','F','F','Failed','Declined')
    insert into config_master values(3,'S','S','F','Failed','No action')
    
    create table tran_detl
    (
    tran_id int,
    tran_swt_status varchar(20),
    tran_icpn_status varchar(20),
    tran_host_status varchar(20),
    tran_status varchar(20),
    tran_action_desc varchar(200)
    )
    
    
    
    insert into tran_detl(tran_id,tran_swt_status,tran_icpn_status,tran_host_status) values(10000001,'S','S','S')
    insert into tran_detl(tran_id,tran_swt_status,tran_icpn_status,tran_host_status) values(10000002,'S','S','F')
    insert into tran_detl(tran_id,tran_swt_status,tran_icpn_status,tran_host_status) values(10000003,'S','S','S')
    insert into tran_detl(tran_id,tran_swt_status,tran_icpn_status,tran_host_status) values(10000004,'S','F','S')
    insert into tran_detl(tran_id,tran_swt_status,tran_icpn_status,tran_host_status) values(10000005,'F','F','S')
    
    
    --Select * from tran_detl
     merge tran_detl t
     using  config_master c
    on c.swt_status = t.tran_swt_status
    and c.icpn_status = t.tran_icpn_status
    and c.host_status = t.tran_host_status
    When matched then
     update  
    set tran_status = c.status,
    tran_action_desc = c.action_desc;
     
    
    Select * from tran_detl
    
    
    
    drop table tran_detl,config_master

    2018년 5월 18일 금요일 오후 2:33
    중재자