title: Update column in set of records only if multiple rows exist with a given value in a different field?
body: <p>My_Table would be something like this:</p>
  
  <pre><code>user_id  shared_field  bool_field
  -------  ------------  ----------
  1        abc           null
  2        def           null
  3        ghi           Y
  4        ghi           null
  5        ghi           null
  6        abc           Y
  7        jkl           null
  </code></pre>
  
  <p>If the bool_field changes for a user who shares the same shared_field with other users (such as user_id 3, 4, and 5 above), only that one user should have a 'Y'. The rest should have null values in the bool_field column. For example, if user_id 4 should now have the 'Y', I have to change user_id 4's bool_field to 'Y', and ensure that user_id 3 and 5 have bool_field values of null.</p>
  
  <p>If the user doesn't share a shared_field value with anyone else, then that bool_field should be null (as in user_id 1 and 2 above).</p>
  
  <p><strong>Update:</strong> added a couple of lines to show that multiple user_ids could share a given shared_field (eg, 1 and 6 both have 'abc'; 3, 4, and 5 all have 'ghi' - only one 'abc' user should have a 'Y', and only one 'ghi' user should have a 'Y' and so on, while the rest have null in their bool_field column; user_ids that don't share a shared_field value, such as user_ids 2 and 7, should all have null in their bool_field column.) Clear as mud, right? ;)</p>
  
  <p>This statement works:</p>
  
  <pre><code>UPDATE my_table
     SET bool_field = (CASE
                         WHEN user_id = 4 THEN 'Y'
                         ELSE NULL
                        END)
   WHERE shared_field = 'ghi'
     AND (SELECT COUNT(shared_field)
            FROM my_table
           WHERE shared_field = 'ghi') &gt; 1;
  </code></pre>
  
  <p>The question: is there some way that I can accomplish this same thing without knowing the shared_field value in advance? For example (and this doesn't work, of course) - <strong>Update</strong>: "of course" means I know this doesn't work because it is not correct Oracle syntax! The point is to give an idea of what I'm trying to do.</p>
  
  <pre><code>UPDATE my_table
     SET bool_field = (CASE
                         WHEN user_id = 4 THEN 'Y'
                         ELSE NULL
                        END)
   WHERE shared_field = (SELECT shared_field FROM my_table WHERE user_id = 4) as sharedVal
     AND (SELECT COUNT(shared_field)
            FROM my_table
           WHERE shared_field = sharedVal) &gt; 1;
  </code></pre>
  
  <hr>
  
  <p><strong>Update</strong>: this is a regular SQL statement - I can't use a stored procedure.</p>