title: MySQL update based on query result
body: <p>i hope this is not considered spamming, today i asked a question:</p>
  <p><a href="https://stackoverflow.com/questions/58289060/mysql-overlaping-time-periods-based-on-criteria">MySQL overlaping time periods based on criteria</a></p>
  <p>now i am trying to do an update query, based on this query:</p>
  <pre><code>SELECT a.id, GROUP_CONCAT(b.id) AS abcd
  FROM new_table a
  LEFT JOIN new_table b ON a.id &lt;&gt; b.id
                        AND a.Block_ID = b.Block_ID
                        AND a.Block_Type = b.Block_Type
                        AND a.Valid = b.Valid
                        AND ((a.Valid_To BETWEEN b.Valid_From AND b.Valid_To)
                              OR (a.Valid_From BETWEEN b.Valid_From AND b.Valid_To)
                              OR (a.Valid_To = b.Valid_From)
                              OR (a.Valid_From = b.Valid_To)
                            )
  GROUP BY a.id;
  </code></pre>
  <p>i am trying to update this table:</p>
  <pre><code>ID  |  Valid_From  |  Valid_To  |  Block_Type  |  Valid  |  Block_ID
  1      5              10           a              TRUE      1
  2      10             15           a              TRUE      1
  3      6              10           a              TRUE      1
  4      3              4            a              FALSE     1
  5      2              4            a              FALSE     1
  6      1              1            a              FALSE     1
  7      1              10           b              TRUE      1
  8      1              10           c              TRUE      2
  9      1              10           c              TRUE      2
  10     1              15           b              TRUE      1
  11     5              10           a              TRUE      1
  </code></pre>
  <p>for my data set, this query returs this:</p>
  <pre><code>ID  |  abcd
  1      2
  2      1,11,3
  3      2
  4      5
  5      4
  6      null
  7      10
  8      9
  9      8
  10     7
  11     2
  </code></pre>
  <p>i inserted new column in my table using</p>
  <pre><code>ALTER TABLE new_table
  ADD COLUMN `Overlaping` VARCHAR(45) NULL AFTER `Block_ID`;
  </code></pre>
  <p>Now i am trying to populate this column using this query, where i would like to update value to 'overlaping' for records where column abcd is not null:</p>
  <pre><code>UPDATE new_table nt2,
  (SELECT a.id, GROUP_CONCAT(b.id) AS abcd
  FROM new_table a
  LEFT JOIN new_table b ON a.id &lt;&gt; b.id
                        AND a.Block_ID = b.Block_ID
                        AND a.Block_Type = b.Block_Type
                        AND a.Valid = b.Valid
                        AND ((a.Valid_To BETWEEN b.Valid_From AND b.Valid_To)
                              OR (a.Valid_From BETWEEN b.Valid_From AND b.Valid_To)
                              OR (a.Valid_To = b.Valid_From)
                              OR (a.Valid_From = b.Valid_To)
                            )
  GROUP BY a.id) as ac
  SET nt2.Overlaping = 'overlaping'
  where ac.abcd is not null;
  </code></pre>
  <p>however this query updates every single row no matter if abcd is null or not, where is the mistake?</p>
  <p>Thank you very much</p>