Details

      Description

      same where clause but, select result : 2rows

      update result : 1 rows

      You can test by test_case.txt
      ========================================

      select * 
        from HR_ASG_MST
       WHERE EMP_ID         = 9165
        AND (( '20160901' >=  EFFCT_STRT_DATE and '20160901' <= EFFCT_END_DATE ) or 
             (  EFFCT_STRT_DATE > '20160901' ));
      

      => 2 rows seleted;

             
      UPDATE HR_ASG_MST
          SET EMP_STAT_CD = 'L',
              UPDT_ID     = '1',
              UPDT_DT     = date_format( now() , '%Y%m%d%H%i%s'),
              LOCK_SEQ    = 2
        WHERE EMP_ID         = 9165
          AND (( '20160901' >=  EFFCT_STRT_DATE and '20160901' <= EFFCT_END_DATE ) or 
              (  EFFCT_STRT_DATE > '20160901' )
              )
              ;
      

      => same where clause but just 1 rows update

      ==========================================================

      i found w/a method is join update statement like below but this is not solution what i want to use

       
      UPDATE HR_ASG_MST  A INNER JOIN HR_ASG_MST B ON A.EMP_ID = B.EMP_ID AND A.EFFCT_STRT_DATE = B.EFFCT_STRT_DATE
                                                   AND (B.EMP_ID         = 9165
      																    AND (( '20160901' >=  B.EFFCT_STRT_DATE and '20160901' <= B.EFFCT_END_DATE )
      																	       or 
      																        (  B.EFFCT_STRT_DATE > '20160901' )
      																        )
      															    )			
          SET A.EMP_STAT_CD = 'T',
              A.UPDT_ID     = 1,
              A.UPDT_DT     = date_format( now() , '%Y%m%d%H%i%s'),
              A.LOCK_SEQ    = 2
              ;
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                seung hoon seung hoon yoo
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: