title: Update foreign key column with another table's primary key
body: <p>Basically, I need to update a column in one table based on a column's value in another table.  This question has been asked at least a couple times here and every answer with high votes states to join the two tables and set the value of a column in one table based on the value of the applicable column in the other table:</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match">SQL update from one Table to another based on a ID match</a></li>
  <li><a href="https://stackoverflow.com/questions/6023367/update-table-based-on-another-table">Update table based on another table</a></li>
  <li><a href="https://stackoverflow.com/questions/27200246/how-to-update-table-based-on-another-table-column">How to update table based on another table column?</a></li>
  <li><a href="https://stackoverflow.com/questions/21150685/update-based-on-another-table-result">Update based on another table result</a></li>
  <li><a href="https://stackoverflow.com/questions/8977222/how-to-update-table-based-on-another-table-in-mysql">how to update table based on another table in mysql?</a></li>
  <li><a href="https://stackoverflow.com/questions/8977222/how-to-update-table-based-on-another-table-in-mysql">how to update table based on another table in mysql?</a></li>
  <li><a href="https://stackoverflow.com/questions/26480203/update-relational-table-based-on-another-table-in-mysql">Update Relational table based on another table in MySQL</a></li>
  <li><a href="https://stackoverflow.com/questions/20849774/how-to-update-a-column-with-another-tables-columns-value">How to update a column with another tables columns value?</a></li>
  </ul>
  
  <p>It doesn't seem to work for me, and either I am not seeing things correct or my conditions are different.</p>
  
  <p><strong>EDIT 1</strong>.  I didn't show it, but <code>supertype</code> also has an <code>accounts_id</code> column, and think that is the column I will be joining on.</p>
  
  <p>I have a supertype and subtype table where the subtype table includes a second field called <code>supertype_id</code> which stores a supertype record's PK (schema shown below).  </p>
  
  <p>I need to update <code>subtype.supertype_id</code> for a given subtype record (i.e. <code>subtype.id=123</code>) with a supertype table's PK based on some other attribute of the supertype table (i.e. <code>supertype.publicId=321</code>).</p>
  
  <p>So, I originally thought I would need some sort of JOIN, however, didn't update any rows as the JOIN doesn't yet exist.</p>
  
  <pre><code>UPDATE subtype
  INNER JOIN supertype ON supertype.id=subtype.id
  SET subtype.supertype_id=supertype.id
  WHERE subtype.id=123 AND supertype.idPublic=321;
  
  UPDATE subtype
  INNER JOIN supertype ON supertype.id=subtype.id AND supertype.idPublic=321
  SET subtype.supertype_id=supertype.id
  WHERE subtype.id=123;
  </code></pre>
  
  <p>I could likely do something like the following, however, question doing so as it differs from all highly voted answers and it also attempts to set <code>suptype.supertype_id</code> to <code>NULL</code> if <code>supertype</code> doesn't have a record with the given <code>idPublic</code> which will result in a foreign key constraint. </p>
  
  <pre><code>UPDATE subtype SET supertype_id = SELECT id FROM superset WHERE idPublic=321;
  </code></pre>
  
  <p>How should this be implemented?</p>
  
  <p><strong>EDIT 2</strong>.  Maybe something like this?</p>
  
  <pre><code>UPDATE suptype sbt
  INNER JOIN supertype spt1 ON spt1.id=sbt.id
  INNER JOIN supertype spt2 ON spt2.accounts_id=spt1.accounts_id
  SET sbt.supertype_id =sbt2.id
  WHERE sbt2.idPublic=321 AND sbt.id=123;
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/Fko5T.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Fko5T.png" alt="enter image description here"></a></p>
  
  <pre><code>CREATE TABLE supertype (
    id INT NOT NULL AUTO_INCREMENT,
    data VARCHAR(45) NULL,
    publicId INT NOT NULL,
    PRIMARY KEY (id),
    INDEX publicIdIdx (publicId ASC))
  ENGINE = InnoDB;
  
  CREATE TABLE subtype (
    id INT NOT NULL,
    supertype_id INT NOT NULL,
    data VARCHAR(45) NULL,
    PRIMARY KEY (id),
    INDEX fk_subtype_supertype1_idx (supertype_id ASC),
    CONSTRAINT fk_subtype_supertype
      FOREIGN KEY (id)
      REFERENCES supertype (id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT fk_subtype_supertype1
      FOREIGN KEY (supertype_id)
      REFERENCES supertype (id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  </code></pre>