Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012

    Unanswered: Sql Help(Update)

    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).

    And i would like yto write in a single statement

    Can I use merge here. please let me know the solution i was struck in middle of my work.

    Thanks & Regards
    Last edited by pallis; 01-24-12 at 01:04.

  2. #2
    Join Date
    Sep 2001
    Chicago, Illinois, USA
    Slapped this together in Notepad, so this is just off the top of my head, but look at this example for a general direction. BTW, I took "nothing" to mean null.

    update  B11
    set     x=
                case B11.APAAP
                    when B11.x=0 then B14.LatestRecord
                    else null
    from    dbo.F03B11 B11
    join    (
            select  RZKCO
                    ,max(RZDGJ) LatestRecord
            from    dbo.F03B14
            by      RZKCO
            ) B14 on
                and B14.RZDCT=B11.RPDCT
                and B14.RZDOC=B11.RPDOC
                and B14.RZSFX=B11.RPSFX

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts