Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10795

not match update rows

    XMLWordPrintable

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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.