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

    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
    pallis
    Last edited by pallis; 01-24-12 at 02:04.

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    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.

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

    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
  •