I'm trying to update two values in the same column, same table on different rows in one process. The DefaultForAxisID tags which CategoryID is the primary vs secondary.

In the BEFORE and AFTER example I need to set the following two things simultaneously to avoid errors.

1. DefaultforAxisID = 2 where ProcedureCodeID = 195837 and CategoryID = 21
2. DefaultforAxisID = null WHERE ProcedureCodeID = 195837 and CategoryID = 22

To put into words, I need to swap the DefaultforAxisID's for CategoryID 21 and 22.

When I execute Fig A, I get following error (Fig B), but it does update the column. I probably could follow it up with a second update but I just don't feel comfortable that it's throwing an error and thus am exploring alternative.

Figure A.

UPDATE ProcedureCodeCategory
SET DefaultForAxisID = null
WHERE CategoryID = 22 and ProcedureCodeID = 195837

Figure B:
[Code: 50000, SQL State: S0001] Clearing an existing default is not permitted. Please select a new deafault for this procedure if this is no longer valid. [Script position: 1795 - 1805]

When I figure out how to achieve this, I will create a stored procedure in the database, and incorporate it into a Python Function.

BEFORE

AFTER

CodePudding user response：

A simple case expression can work here easily enough.

update YourTable
set DefaultforAxisID = case CategoryID when 21 then 2 when 22 then NULL else DefaultforAxisID end 
where CategoryID in (21, 22)