2

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

ProcedureCodeCategoryID ProcedureCodeID CategoryID IsInherited DefaultforAxisID
998743 195837 21 false (null)
998509 195837 22 false 2
998742 195837 314 false (null)
998510 195837 316 false 3

AFTER

ProcedureCodeCategoryID ProcedureCodeID CategoryID IsInherited DefaultforAxisID
998743 195837 21 false 2
998509 195837 22 false (null)
998742 195837 314 false (null)
998510 195837 316 false 3
Improve this question
1
  • FYI Having such huge heading doesn't make it more readable... hence why I reduced them. Also the error shows better quoted than as code. Commented Nov 17, 2021 at 20:37

1 Answer 1

Reset to default
2

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I ran this, it did not update DefaultForAxisID = 2 when CategoryID = 21. It left DefaultforAxisID = null
Ahh yes...my bad. I suspect you really want a more dynamic version but your requirement is unclear.
I updated my question; I hope this clarifies things. Let me know if it is still unclear.
I changed the Where to WHERE ProcedureCodeID = 195837 and it worked!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.