Hi guys, would like to check how to write a update query with inner join statement ? The case is like that, I need to update PRD_ID on TBL A as below, based on the lookup table information. The key are based on column A03 from both table with condition only select those records in LOOKUP_TBL where PRD_ID = 110001 then update to TBL_A for those matched data and FIX_FLT = 1
I've a update query and it work well in SQL SERVER but not ORACLE
Update A set PRD_ID = B.PRD_ID
FROM TBL_A A inner join LOOKUP_TBL B
on A.A03 = B.A03
AND A.FIX_FLT = 1 AND B.PRD_ID = '110001';
TBL_A
| PRD_ID | A03 | FIX_FLT | TXN | DATE |
| 1 | A1 | 1 | 1 | 10/23/2010 |
| 1 | A2 | 1 | 1 | 10/24/2010 |
| 1 | A3 | 3 | 2 | 10/25/2010 |
| 1 | A4 | 3 | 2 | 10/26/2010 |
| 1 | A5 | 3 | 1 | 10/27/2010 |
LOOKUP_TBL
| PRD_ID | A03 | REMARK |
| 110001 | A1 | NULL |
| 110001 | A2 | NULL |
| 110005 | A3 | NULL |
| 110005 | A4 | NULL |
| 110001 | A5 | NULL |