title: How to update redshift column: simple text replacement
body: <p>I have a large <code>target</code> table with columns (<code>id</code>, <code>value</code>). I want to update <code>value='old'</code> to <code>value='new'</code>. </p>
  
  <p>The simplest way would be to <code>UPDATE target SET value='new' WHERE value='old';</code></p>
  
  <p>However, this deletes and creates new rows and is not recommended, possibly. So I tried to do a merge column update:</p>
  
  <pre><code># staging
  CREATE TABLE stage (LIKE target INCLUDING DEFAULTS);
  INSERT INTO stage (SELECT id, value FROM target WHERE value=`old`);
  UPDATE stage SET value='new' WHERE value='old'; # ??? how do you update value?
  
  # merge
  begin transaction;
  UPDATE target 
  SET value = stage.value FROM stage
  WHERE target.id = stage.id and target.distkey = stage.distkey; # collocated join?
  end transaction;
  
  DROP TABLE stage;
  </code></pre>
  
  <ol>
  <li>This can't be the best way of creating the table stage: I have to do all these <code>UPDATE</code> delete/writes when I update this way. Is there a way to do it in the <code>INSERT</code>?</li>
  <li>Is it necessary to force the collocated join when I use <code>CREATE TABLE LIKE</code>?</li>
  </ol>