Thread: Sql Help(Update)
01-24-12, 00:42 #1Registered User
- Join Date
- Jan 2012
Unanswered: Sql Help(Update)
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.
01-24-12, 07:43 #2Registered User
- 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 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.RPSFXKen
Maverick Software Design
(847) 864-3600 x2