Sql Help(Update)

pallis · #1 (**permalink**) 01-24-12, 00:42

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

PracticalProgram · #2 (**permalink**) 01-24-12, 07:43

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

Internet Services

Web Development

Digital Marketing

Consumer Tech