0

I have a query like this

SELECT 
    MVM.date, MVM.time, MVM.TE, MVM.SO, MVM.ST, MVM.PA, MVM.RE, **MVM.QTY**, **ADT.QTY**
FROM 
    (SELECT 
         A.*, 
         RANK() OVER (PARTITION BY A.SO, A.ST, A.PA, A.DC  
                      ORDER BY A.AM) AS RANK 
     FROM 
         FILE1 A 
     WHERE 
         A.TR IN ('73', '74') 
         AND A.LS >= '2023152') ADT
JOIN 
    (SELECT 
         A.*, 
         RANK() OVER (PARTITION BY A.SO, A.ST, A.PA, A.RE 
                      ORDER BY A.AD) AS RANK 
     FROM 
         FILE2 A 
     WHERE 
         A.TR = '06' 
         AND A.AD >= '2023152') MVM ON ADT.SO = MVM.SO 
                                    AND ADT.ST = MVM.ST 
                                    AND ADT.PA = MVM.PA 
                                    AND ADT.DC = MVM.RE 
                                    AND ADT.RANK = MVM.RANK
WHERE 
    1 = 1
    AND MVM.AD <> ADT.AM
    AND TRIM(MVM.ST) || TRIM(MVM.SO) || TRIM(MVM.PA) || TRIM(MVM.RE) IN (SELECT DISTINCT (TRIM(ST) || TRIM(SO) || TRIM(PA) || TRIM(DC)) FROM FILE1 WHERE TR IN ('73E','74E') AND LS >='2023152')

I want to update MVM.QTY = ADT.QTY but DB2 is not comfortable with JOIN in an UPDATE statement.

So please help me. Thank you!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
thang hd
  • 1
  • 1
  • 1
    What's your Db2 platform and version? – Mark Barinstein Aug 02 '23 at 10:22
  • 1
    Does this answer your question? [INNER JOIN in UPDATE sql for DB2](https://stackoverflow.com/questions/4184209/inner-join-in-update-sql-for-db2) – Charles Aug 02 '23 at 12:08
  • Thank you for your help! I have found solution at here https://stackoverflow.com/questions/54320681/update-with-inner-join-not-working-in-db2 – thang hd Aug 03 '23 at 09:25

0 Answers0