locked
Help in update statement RRS feed

  • Question

  • Hi all,
    I would like to write on update using these conditions for one of my table could you help me to resolve this. 
    table is f03b11 and the column is x
      Based on below condition i need a update statement 
    Step 1 If invoice open amount F03B11.APAAP <> 0 then X = Blank                                            
    Step 2 If invoice open amount F03B11.APAAP =  0 then X =  F03B14.RZDGJ, match F03B11.RPKCO = F03B14.RZKCO AND F03B11.RPDCT = F03B14.RZDCT AND F03B11.RPDOC = F03B14.RZDOC AND F03B11.RPSFX = F03B14.RZSFX
    Step 3 If you find multiple matches in F03B14 then select and update the latest date (RZDGJ).
    Can I use merge here. please let me know the solution i was struck in middle of my work.
    --
    Thanks & Regards
    pallis

    Tuesday, January 24, 2012 6:00 AM

Answers

  • UPDATE f03b11
       SET x =
              CASE
                 WHEN ((SELECT f03012.aiclmg
                          FROM f03012
                         WHERE f03b11.rpan8 = f03012.aian8
                           AND f03b11.rpco = f03012.aico)) IS NULL
                    THEN (SELECT f03012.aiclmg
                            FROM f03012
                           WHERE (f03012.aico) = '00000')
              END;
    • Marked as answer by contact pallis Tuesday, January 24, 2012 1:24 PM
    Tuesday, January 24, 2012 1:24 PM

All replies

  • i am not sure if this is what you want, if its not the below, please try to rephrase 

    UPDATE  F03b11
    
    SET  F03b11.x = CASE 
                    WHEN F03b11.x <> 0 THEN ''
                    WHEN F03b11.x = 0  THEN F03B14.rzdgj
                    ELSE MAX(F03B14.rzdgj) END 
    WHERE F03b11  LEFT JOIN F03B14 
                    ON F03B11.RPKCO = F03B14.RZKCO 
                    AND F03B11.RPDCT = F03B14.RZDCT 
                    AND F03B11.RPDOC = F03B14.RZDOC 
                    AND F03B11.RPSFX = F03B14.RZSFX
    


    • Proposed as answer by Dkuud Tuesday, January 24, 2012 7:46 AM
    Tuesday, January 24, 2012 6:35 AM
  • Thanks Dilip i will let you know.. 
    Tuesday, January 24, 2012 6:37 AM
  • could you help me even in this update, 

    Y table name X column name

    Step 1 X = F03012.AICLMG  
      Match F03B11.RPAN8 =  F03012.AIAN8 and F03B11.RPCO = F03012.AICO
    Step 2 If no match found  for F03B11.RPCO then select record  (F03012.AICO) = '00000'

    Tuesday, January 24, 2012 8:41 AM
  • UPDATE f03b11
       SET x =
              CASE
                 WHEN ((SELECT f03012.aiclmg
                          FROM f03012
                         WHERE f03b11.rpan8 = f03012.aian8
                           AND f03b11.rpco = f03012.aico)) IS NULL
                    THEN (SELECT f03012.aiclmg
                            FROM f03012
                           WHERE (f03012.aico) = '00000')
              END;
    • Marked as answer by contact pallis Tuesday, January 24, 2012 1:24 PM
    Tuesday, January 24, 2012 1:24 PM