[MDEV-10795] not match update rows Created: 2016-09-12  Updated: 2016-09-19  Resolved: 2016-09-19

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 10.0.26, 10.1.14
Fix Version/s: 5.5.51, 10.1.17, 10.0.27

Type: Bug Priority: Major
Reporter: seung hoon yoo Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux centos 6.6

Attachments: Text File test_case.TXT    
Issue Links:
Duplicate
is duplicated by MDEV-10228 Delete missing rows with OR conditions Closed

 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
 
        ;



 Comments   
Comment by Elena Stepanova [ 2016-09-19 ]

This problem was fixed in scope of MDEV-10228 in 10.0.27. Please upgrade.

Generated at Thu Feb 08 07:44:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.