title: Updating a column with JSON data in another table
body: <p>I have seen a lot on JSON and SQL Server but haven't been able to find what I am looking for.</p>
  
  <p>I want to update columns in one table by retrieving JSON values from another table.</p>
  
  <p>Lets say I have the below table:</p>
  
  <p><strong>table : people</strong></p>
  
  <pre><code>+-------+-----------+
  |   id  |   name    |  
  +-------+-----------+
  |   1   |   John    |      
  |   2   |   Mary    |     
  |   3   |   Jeff    |       
  |   4   |   Bill    |       
  |   5   |   Bob     |   
  +-------+-----------+
  </code></pre>
  
  <p>And lets pretend I have another table filled with rows of JSON like the following:</p>
  
  <p><strong>table : archive</strong></p>
  
  <pre><code>+-------+----------------------------------------------------------------+
  |   id  |   json                                                         |  
  +-------+----------------------------------------------------------------+
  |   1   |[{ "Column":"name","values": { "old": "Jeff", "new": "John"}}]  |      
  |   2   |[{ "Column":"name","values": { "old": "Rose", "new": "Mary"}}]  |     
  +-------+----------------------------------------------------------------+
  </code></pre>
  
  <p>Now the idea is to change Johns name to Jeff.</p>
  
  <pre><code>UPDATE people
  SET name = JSON_QUERY(archive.json, '$values.old')
  WHERE ID = 1
  </code></pre>
  
  <p>The above SQL may make no sense but I'm just trying to get across my current logic of what I'm trying to do. I hope it makes some sense.</p>
  
  <p>If more information is needed please ask.</p>