title: Postgres update with an inner join across 2 tables?
body: <p>I have 3 tables in my local Postgres database:</p>
  
  <pre><code>[myschema].[animals]
  --------------------
  animal_id
  animal_attrib_type_id (foreign key to [myschema].[animal_attrib_types])
  animal_attrib_value_id (foreign key to [myschema].[animal_attrib_values])
  
  [myschema].[animal_attrib_types]
  --------------------------------
  animal_attrib_type_id
  animal_attrib_type_name
  
  [myschema].[animal_attrib_values]
  --------------------------------
  animal_attrib_value_id
  animal_attrib_value_name
  </code></pre>
  
  <p>At runtime I will know the <code>animal_id</code>. I need to run SQL to update the <code>animal_attribute_value_name</code> associated with this item, so something like:</p>
  
  <pre><code>UPDATE
      animal_attribute_values aav
  SET
      aav.animal_attribute_value_name = 'Some new value'
  WHERE
      # Somehow join from the provided animal_id???
  </code></pre>
  
  <p>I may have to do some kind of nested <code>SELECT</code> or <code>INNER JOIN</code> inside the <code>WHERE</code> clause, but not sure how to do this. Thanks in advance!</p>
  
  <p><strong>Edit</strong>:</p>
  
  <p>Let's say I have an <code>animal</code> record with the following values:</p>
  
  <pre><code>[myschema].[animals]
  --------------------
  animal_id = 458
  animal_attrib_type_id = 38
  animal_attrib_value_id = 23
  </code></pre>
  
  <p>And the corresponding <code>animal_attrib_value</code> (with id = 23) has the following values:</p>
  
  <pre><code>[myschema].[animal_attrib_values]
  --------------------------------
  animal_attrib_value_id = 23
  animal_attrib_value_name = 'I am some value that needs to be changed.'
  </code></pre>
  
  <p>At runtime, I only have the <code>animal_id</code> (458). I need to look up the corresponding <code>animal_attrib_value</code> (23) and change its <code>animal_attrib_value_name</code> to <code>'Some new value'</code>, all inside of a single UPDATE statement.</p>