-2

I have a table with a field called gender. It's an int. 1 = male, 2 = female. Here's what was done, in the same script

UPDATE table
SET GENDER = 9
WHERE GENDER = 1
AND (other criteria);

UPDATE table
SET GENDER = 1
WHERE GENDER = 2
AND (other criteria);

UPDATE table
SET GENDER = 2
WHERE GENDER = 9
AND (other criteria);

In each case, the (other criteria) were EXACTLY the same.

It would be expected that this would swap out the genders, changing all the male to female, and all the female to male. That's not what happened. Everything went to female.

They did a restore to bring the old data back, and then the dba running it ran each update separately, one at a time. That time it worked as expected.

What gives?

I'm not asking how to solve the problem of getting the genders swapped. That's already long since been solved. I am asking why the code as written didn't provide the expected result.

Here are the exact queries that were all run as part of a .sql script...at once.

update t_Ship_beneficiary_contact 
set C_BENEFICIARY_GENDER = 9 
where C_BENEFICIARY_GENDER=1 
and c_ship_case_number like '%API' 
and ID_HIERARCHY in (select HIERARCHY_ID from etk_hierarchy where node_id between 408 and 473) 
and id < 25618804;


update t_Ship_beneficiary_contact 
set C_BENEFICIARY_GENDER = 1 
where C_BENEFICIARY_GENDER=2 
and c_ship_case_number like '%API' 
and ID_HIERARCHY in (select HIERARCHY_ID from etk_hierarchy where node_id between 408 and 473) 
and id < 25618804;


update t_Ship_beneficiary_contact 
set C_BENEFICIARY_GENDER = 2 
where C_BENEFICIARY_GENDER=9 
and c_ship_case_number like '%API' 
and ID_HIERARCHY in (select HIERARCHY_ID from etk_hierarchy where node_id between 408 and 473) 
and id < 25618804;

I didn't choose this method. I'm trying to help someone else. Trying to figure out why this occurred. I already gave them a revised single update statement with a CASE statement that would do the same job correctly.

It was not run in a transaction. I imagine it could be possible update2 failed for some reason, but they didn't report any errors coming out of the script.

I spun up a quick database with one table and one field, and dispensed with the 'other criteria'. These were only used in order to select specific rows on which to perform the actions. I reran these three update statements all at once, and it worked as advertised.

Improve this question
2
  • 2
    @J.D.Walker Your intuition is correct about how this should work. The only explanation really is that there was user error or concurrency issues involved, which unfortunately there's probably no way of knowing after the fact. For instance, maybe the DBA accidentally had only the second two update statements highlighted when they ran the script, or one or more of the updates encountered runtime errors that the DBA didn't report to you, or other concurrent updates were running that caused the unexpected results, etc. – Josh Darnell Dec 17 '19 at 19:42
  • Is it possible that in the first run the second query failed with some statement-aborting (but not batch-aborting) error? That would produce all GENDER = 2. sommarskog.se/error-handling-I.html#statementbatch – David Browne - Microsoft Dec 17 '19 at 21:01
2
UPDATE table
SET GENDER = CASE WHEN GENDER = 1 THEN 2
                  WHEN GENDER = 2 THEN 1
             END
WHERE (other criteria)
/*  AND GENDER IN (1, 2) */ ;

or

UPDATE table
SET GENDER = 3 - GENDER
WHERE (other criteria)
/*  AND GENDER IN (1, 2) */ ;
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.