Updating multiple rows based on column value of one Row

Question

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.

Karl Kieninger · Accepted Answer · 2014-04-03 00:27:54Z

1

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.

answered Apr 3, 2014 at 0:27

Karl Kieninger

9,1692 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abhi.Net Over a year ago

Thank you for your answer. From what I know triggers are considered to be really and and should only be used for simple purposes like auditing. Also it is recommended to update stored proc instead of creating triggers as they can do everything that a trigger does. Thanks for the 'Default' tip (did not know) but it was just an example

Karl Kieninger Over a year ago

Triggers aren't all bad; triggers should be used with caution. In part it is a question of scale. In this case, the advantage of a trigger is you can apply it in one place and have it "fix" all updates/insert done from anywhere. Then, as time permits, you move all the updates/inserts and finally can remove the trigger. This trigger would simply compared to a typical audit trigger.

Collectives™ on Stack Overflow

Updating multiple rows based on column value of one Row

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related