Three updates in order in the same script won't work as intended, why?

Question

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.

Answer 1

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) */ ;