title: set column value based on distinct values in another column
body: <p>I am trying to do something very similar to this question: <a href="https://stackoverflow.com/questions/4908245/mysql-updateing-row-based-on-other-rows">mysql - UPDATEing row based on other rows</a></p>
  
  <p>I have a table, called modset, of the following form:</p>
  
  <pre><code>member    year    y1    y2    y3    y1y2    y2y3    y1y3    y1y2y3 
  a         1       0     0     0     0       0       0       0
  a         2       0     0     0     0       0       0       0
  a         3       0     0     0     0       0       0       0
  b         1       0     0     0     0       0       0       0
  b         2       0     0     0     0       0       0       0
  c         1       0     0     0     0       0       0       0
  c         3       0     0     0     0       0       0       0
  d         2       0     0     0     0       0       0       0
  </code></pre>
  
  <p>Columns 3:9 are binary flags to indicate which combination of years the member has records in.  So I wish the result of an SQL update to look as follows:</p>
  
  <pre><code>member    year    y1    y2    y3    y1y2    y2y3    y1y3    y1y2y3 
  a         1       0     0     0     0       0       0       1
  a         2       0     0     0     0       0       0       1
  a         3       0     0     0     0       0       0       1
  b         1       0     0     0     1       0       0       0
  b         2       0     0     0     1       0       0       0
  c         1       0     0     0     0       0       1       0
  c         3       0     0     0     0       0       1       0
  d         2       0     1     0     0       0       0       0
  </code></pre>
  
  <p>The code in the question linked above does something very close but only when it is a count of the distinct years in which the member has records.  I need to base the columns on the specific values of the years in which the member has records.</p>
  
  <p>Thanks in advance!</p>
  
  <p><strong>SOLUTION</strong></p>
  
  <pre><code>SELECT member,
      case when min(distinct(year)) = 1 and max(distinct(year)) = 1 then 1 else 0 end y1,
      case when min(distinct(year)) = 1 and max(distinct(year)) = 2 then 1 else 0 end y1y2,
      case when min(distinct(year)) = 1 and max(distinct(year)) = 3 and count(distinct(year)) = 2 then 1 else 0 end y1y3,
      case when min(distinct(year)) = 1 and max(distinct(year)) = 3 and count(distinct(year)) = 3 then 1 else 0 end y1y2y3,
      case when min(distinct(year)) = 2 and max(distinct(year)) = 2 then 1 else 0 end y2,
      case when min(distinct(year)) = 2 and max(distinct(year)) = 3 then 1 else 0 end y2y3,
      case when min(distinct(year)) = 3 then 1 else 0 end y3
  INTO temp5
  FROM modset
  GROUP BY member;
  
  UPDATE modset M
  SET y1 = T.y1, y2 = T.y1, y3 = T.y3, y1y2 = T.y1y2, y1y3 = T.y1y3, y2y3 = T.y2y3, y1y2y3 = T.y1y2y3
  FROM temp5 T
  WHERE T.member = M.member;
  </code></pre>