title: Update visibility of record on the basis of the value of multiple other records
body: <p>I am using the following query in a stored procedure to update cvUpd.visible to 0 or 1 when cvFrom.value is 1, 2 or 3. As can be seen I use the OptionRestriction table to determine what optionvalues in configvalue should be visible or not, depending on whether the value of other optionvalues in configvalue is 1 or 0 (I only included 1's and 0's for the value in the table to keep the example simple).</p>
  
  <pre><code>UPDATE cvUpd
  SET cvUpd.visible = case cvFrom.value when 1 then 0 when 2 then 0 when 3 then 0 else 1 end 
  FROM ConfigValue cvUpd
  INNER JOIN OptionRestriction r ON cvUpd.ConfigurationID=67571
     AND cvUpd.FeatureID = r.Feature_ID_2
     AND cvUpd.OptionValue= r.OptionValue_2
  INNER JOIN ConfigValue cvFrom ON cvFrom.FeatureID =r.Feature_ID_1  
     AND cvFrom.ConfigurationID = cvUpd.ConfigurationID 
     AND cvFrom.OptionValue=r.OptionValue_1
     AND cvFrom.FeatureID = r.Feature_ID_1
     AND cvFrom.value=r.value
  </code></pre>
  
  <p>This is my OptionRestriction table:</p>
  
  <pre><code>+---------------+---------------+-------+--------------+---------------+---------+
  | Feature_ID_1  | OptionValue_1 | value | Feature_ID_2 | OptionValue_2 | visible |
  +---------------+---------------+-------+--------------+---------------+---------+
  |           399 |             6 |     1 |          403 |             2 |       0 |
  |           399 |             6 |     0 |          403 |             2 |       1 |
  |           399 |            14 |     1 |          403 |             2 |       0 |
  |           399 |            14 |     0 |          403 |             2 |       1 |
  |           776 |             1 |     1 |          403 |             2 |       0 |
  |           776 |             1 |     0 |          403 |             2 |       1 |
  +---------------+---------------+-------+--------------+---------------+---------+
  </code></pre>
  
  <p>The code to create the table above is:</p>
  
  <pre><code>CREATE TABLE OptionRestriction(
     Feature_ID_1  INTEGER  NOT NULL
    ,OptionValue_1 INTEGER  NOT NULL
    ,value         INTEGER  NOT NULL
    ,Feature_ID_2  INTEGER  NOT NULL
    ,OptionValue_2 INTEGER  NOT NULL
    ,visible       BIT  NOT NULL
  );
  INSERT INTO OptionRestriction(Feature_ID_1,OptionValue_1,value,Feature_ID_2,OptionValue_2,visible) VALUES (399,6,1,403,2,0);
  INSERT INTO OptionRestriction(Feature_ID_1,OptionValue_1,value,Feature_ID_2,OptionValue_2,visible) VALUES (399,6,0,403,2,1);
  INSERT INTO OptionRestriction(Feature_ID_1,OptionValue_1,value,Feature_ID_2,OptionValue_2,visible) VALUES (399,14,1,403,2,0);
  INSERT INTO OptionRestriction(Feature_ID_1,OptionValue_1,value,Feature_ID_2,OptionValue_2,visible) VALUES (399,14,0,403,2,1);
  INSERT INTO OptionRestriction(Feature_ID_1,OptionValue_1,value,Feature_ID_2,OptionValue_2,visible) VALUES (776,1,1,403,2,0);
  </code></pre>
  
  <p>This is my ConfigValue table:</p>
  
  <pre><code>+-----------------+-----------+-------------+-------+---------+
  | ConfigurationID | FeatureID | OptionValue | Value | Visible |
  +-----------------+-----------+-------------+-------+---------+
  |           67571 |       399 |           6 |     1 |       1 |
  |           67571 |       399 |          14 |     0 |       1 |
  |           67571 |       776 |           1 |     0 |       1 |
  |           67571 |       403 |           2 |     0 |       0 |
  +-----------------+-----------+-------------+-------+---------+
  </code></pre>
  
  <p>The code to create the table above is:</p>
  
  <pre><code>CREATE TABLE ConfigValue(
     ConfigurationID INTEGER  NOT NULL
    ,FeatureID       INTEGER  NOT NULL
    ,OptionValue     INTEGER  NOT NULL
    ,Value           INTEGER  NOT NULL
    ,Visible         BIT  NOT NULL
  );
  INSERT INTO ConfigValue(ConfigurationID,FeatureID,OptionValue,Value,Visible) VALUES (67571,399,6,1,1);
  INSERT INTO ConfigValue(ConfigurationID,FeatureID,OptionValue,Value,Visible) VALUES (67571,399,14,0,1);
  INSERT INTO ConfigValue(ConfigurationID,FeatureID,OptionValue,Value,Visible) VALUES (67571,776,1,0,1);
  INSERT INTO ConfigValue(ConfigurationID,FeatureID,OptionValue,Value,Visible) VALUES (67571,403,2,0,0);
  </code></pre>
  
  <p>The problem with my query is that now only optionvalue 6 from feature 399 is able to update visible from optionvalue 2 from feature 403 to 0 or 1, depending whether its value is 0 or 1.</p>
  
  <p>What I actually want to achieve is to update visible from optionvalue 2 from feature 403 to 1 when the values from optionvalue 6 and 14 from feature 399 and optionvalue 1 from feature 776 is 0, like this:</p>
  
  <pre><code>+-----------------+-----------+-------------+-------+---------+
  | ConfigurationID | FeatureID | OptionValue | Value | Visible |
  +-----------------+-----------+-------------+-------+---------+
  |           67571 |       399 |           6 |     0 |       1 |
  |           67571 |       399 |          14 |     0 |       1 |
  |           67571 |       776 |           1 |     0 |       1 |
  |           67571 |       403 |           2 |     0 |       1 |
  +-----------------+-----------+-------------+-------+---------+
  </code></pre>
  
  <p>When one or more values of optionvalues 6, 14, 1 from feature 399 and 776 are updated to 1, visible from optionvalue 2 from feature 403 should be updated to 0, like:</p>
  
  <pre><code>+-----------------+-----------+-------------+-------+---------+
  | ConfigurationID | FeatureID | OptionValue | Value | Visible |
  +-----------------+-----------+-------------+-------+---------+
  |           67571 |       399 |           6 |     1 |       1 |
  |           67571 |       399 |          14 |     0 |       1 |
  |           67571 |       776 |           1 |     0 |       1 |
  |           67571 |       403 |           2 |     0 |       0 |
  +-----------------+-----------+-------------+-------+---------+
  
  +-----------------+-----------+-------------+-------+---------+
  | ConfigurationID | FeatureID | OptionValue | Value | Visible |
  +-----------------+-----------+-------------+-------+---------+
  |           67571 |       399 |           6 |     0 |       1 |
  |           67571 |       399 |          14 |     1 |       1 |
  |           67571 |       776 |           1 |     0 |       1 |
  |           67571 |       403 |           2 |     0 |       0 |
  +-----------------+-----------+-------------+-------+---------+
  
  +-----------------+-----------+-------------+-------+---------+
  | ConfigurationID | FeatureID | OptionValue | Value | Visible |
  +-----------------+-----------+-------------+-------+---------+
  |           67571 |       399 |           6 |     0 |       1 |
  |           67571 |       399 |          14 |     0 |       1 |
  |           67571 |       776 |           1 |     1 |       1 |
  |           67571 |       403 |           2 |     0 |       0 |
  +-----------------+-----------+-------------+-------+---------+
  
  +-----------------+-----------+-------------+-------+---------+
  | ConfigurationID | FeatureID | OptionValue | Value | Visible |
  +-----------------+-----------+-------------+-------+---------+
  |           67571 |       399 |           6 |     1 |       1 |
  |           67571 |       399 |          14 |     1 |       1 |
  |           67571 |       776 |           1 |     0 |       1 |
  |           67571 |       403 |           2 |     0 |       0 |
  +-----------------+-----------+-------------+-------+---------+
  
  +-----------------+-----------+-------------+-------+---------+
  | ConfigurationID | FeatureID | OptionValue | Value | Visible |
  +-----------------+-----------+-------------+-------+---------+
  |           67571 |       399 |           6 |     0 |       1 |
  |           67571 |       399 |          14 |     1 |       1 |
  |           67571 |       776 |           1 |     1 |       1 |
  |           67571 |       403 |           2 |     0 |       0 |
  +-----------------+-----------+-------------+-------+---------+
  
  +-----------------+-----------+-------------+-------+---------+
  | ConfigurationID | FeatureID | OptionValue | Value | Visible |
  +-----------------+-----------+-------------+-------+---------+
  |           67571 |       399 |           6 |     1 |       1 |
  |           67571 |       399 |          14 |     1 |       1 |
  |           67571 |       776 |           1 |     1 |       1 |
  |           67571 |       403 |           2 |     0 |       0 |
  +-----------------+-----------+-------------+-------+---------+
  </code></pre>
  
  <p>How can I achieve the above using my update query and the OptionRestriction and ConfigValue tables?</p>