Hi,
I have two tables like as follows and I need to update Table A (Name field) base on table B. I need help on this.
Current Table A :
ID Line Code Amount Name
111 1 MSCC 500 <null>
111 2 MSCC 300 <null>
111 3 MSCC 300 <null>
111 4 MSCD 300 <null>
111 5 TRFC 500 <null>
111 6 TRFC 500 <null>
111 7 TRFC 800 <null>
222 1 MSCC 300 <null>
Table B
No HD_ID Line Description Dr_AMT Cr_AMT
111 2201 1 descA 300
111 2202 1 descB 500
111 2202 2 descC 500
111 2203 1 descD 300
111 2205 1 descE 300
111 2206 1 descF 400
111 2207 1 descG 600
111 2209 3 descH
222 2210 1 descI 300
- Find Table A (ID) = Table B (No)
- Find Table A Amount = Table B Dr_AMT / Cr_AMT
- If Table A (Code) like '%C' then find Table B Dr_AMT field
- If Table A (Code) like '%D' then find Table B Cr_AMT field
- If return more than one records, then update the Table A (Name) by sequence as below
Expecting result :
Update Name in Table A
ID Line Code Amount Name
111 1 MSCC 500 descB <-- If Code like '%C' then find Table B Dr_AMT field = Amount 500 (1st)
111 2 MSCC 300 descA <-- If Code like '%C' then find Table B Dr_AMT field = Amount 300 (1st)
111 3 MSCC 300 descE <-- If Code like '%C' then find Table B Dr_AMT field = Amount 300 (2nd)
111 4 MSCD 300 descD <-- If Code like '%D' then find Table B Cr_AMT field = Amount 300 (1st)
111 5 TRFC 500 descC <-- If Code like '%C' then find Table B Dr_AMT field = Amount 500 (2nd)
111 6 TRFC 500 <null> <-- If Code like '%C' then find Table B Dr_AMT field = Amount 500 (3rd)
111 7 TRFC 800 <null> <-- If Code like '%C' then find Table B Dr_AMT field = Amount 800 (1st)
222 1 MSCC 300 descI <-- If Code like '%C' then find Table B Dr_AMT field = Amount 300 (1st)
Thanks a lot~