Databases
StackOverflow2013
Postsdbo
POPostgres update with an inner join across 3 tables?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPostgres update with an inner join across 3 tables?
    text
    copied!<p>This morning I asked <a href="https://stackoverflow.com/questions/12600608/postgres-update-with-an-inner-join-across-2-tables">this</a> very similar question, and it was answered beautifully.</p> <p>However, after reviewing the play, I see that my actual problem is <em>slightly</em> more complicated than what I described in that question. Basically, I have 3 Postgres tables:</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>So, I might have an <code>animal</code> record like so:</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_type</code> (with id = 38) has the following values:</p> <pre><code>[myschema].[animal_attrib_types] -------------------------------- animal_attrib_type_id = 38 animal_attrib_type_name = 'animals.should-make-noise' </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_type_id = 23 animal_attrib_type_name = 'true' </code></pre> <p>So, the same <code>animal</code> record can have multiple type/value pairs. In this case the animal had an <code>animal_attrib_type_name</code> of "<code>animals.should-make-noise</code>" corresponding to an <code>animal_attrib_value_name</code> of "<code>true</code>".</p> <p>At runtime, I will only have the <code>animal_id</code> (i.e, 458) and <code>animal_attrib_type_id</code> (i.e, 38). I need to be able to look up the appropriate <code>animal_attrib_value_name</code> corresponding to that given <code>animal_id</code> and <code>animal_attrib_type_id</code> only, and then update its value to some static text ('true' or 'false'); all from within the same UPDATE statement.</p> <p>The answer in the above-referenced question was correct for the problem I stated, but since the same animal has 0+ type/value combos I actually need a slightly different SQL statement. Thanks in advance!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload