CODEWITHSUNDEEP
sql
Abhi.Net
Abhi.Net

Reputation: 772

Updating multiple rows based on column value of one Row

I have two very simple INSERT and UPDATE Statements. The way our system is setup we can only update one row at a time through our front end( although this can be changed but will require quite a few code updates)

Process  Scenario  Default
  1         1         True
  1         2         False
  1         3         False
  2         1         False
  2         2         True

If you have a look at above sample table, Only one scenario in a given process can have default= true (for Process 1 it is scenario 1, for Process 2 it is Scenario 2

We didnt had the Default column earlier and was only added yesterday so the code behind and sql query was not designed to handle only one default =true per scenario.

My question is - Is is better to update my INSERT and UPDATE statements so that if I get Default value as true for a scenario I then loop through all the scenarios for that process and set the Default to false or modify my code and write a new stored proc that updates the table separately.

Upvotes: 0

Views: 47

Answers (1)

Karl Kieninger
Karl Kieninger

Reputation: 9129

In the long run, I would convert your code to use stored procs for inserts and updates and manage defaults within those.

As an immediate fix that could be implemented unilateral in the database, use a trigger to remove any previous Default = True when a new value is set at default.

Oh, and if it's not to late, do not use Default as a column name. Default is a reserved keyword. Prefer something like IsDefault.

Upvotes: 1

Related Questions