Postgres update with an inner join across 2 tables?

Question

I have 3 tables in my local Postgres database:

[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

At runtime I will know the animal_id. I need to run SQL to update the animal_attribute_value_name associated with this item, so something like:

UPDATE
    animal_attribute_values aav
SET
    aav.animal_attribute_value_name = 'Some new value'
WHERE
    # Somehow join from the provided animal_id???

I may have to do some kind of nested SELECT or INNER JOIN inside the WHERE clause, but not sure how to do this. Thanks in advance!

Edit:

Let's say I have an animal record with the following values:

[myschema].[animals]
--------------------
animal_id = 458
animal_attrib_type_id = 38
animal_attrib_value_id = 23

And the corresponding animal_attrib_value (with id = 23) has the following values:

[myschema].[animal_attrib_values]
--------------------------------
animal_attrib_value_id = 23
animal_attrib_value_name = 'I am some value that needs to be changed.'

At runtime, I only have the animal_id (458). I need to look up the corresponding animal_attrib_value (23) and change its animal_attrib_value_name to 'Some new value', all inside of a single UPDATE statement.

nothing, just there for visual appeal; helps me see "segments" (schemas, tables, fields) that much easier; guess i borrowed it from my MS SQL days — IAmYourFaja, Sep 26 '12 at 11:27
Your intention is not clear. Do you want to update an "animal", changing one of its attributes to (point to)a new value? Also: actual table definitions would help. — wildplasser, Sep 26 '12 at 11:35
Sorry for the confusion - At runtime I will know the animal whose corresponding animal_attrib_value I want to update. Notice that on the `animals` table I have a foreign key to `animal_attrib_values`. This foreign key should not change, only the corresponding `animal_attrib_value_name`. — IAmYourFaja, Sep 26 '12 at 11:40
I have edited my question because I realize the `animal_attribute_type` doesn't factor into this at all; I should be able to update the animal's corresponding `animal_attrib_value_name` with just the `animal_id` alone. — IAmYourFaja, Sep 26 '12 at 11:42
It is clear that you have some (reduced) kind of EntityAttributeValue( EAV) data model, but your intention is still not clear. — wildplasser, Sep 26 '12 at 11:45
@wildplasser - I have edited my answer with a specific example. Please let me know if its still unclear as to what I'm asking. Thanks again! — IAmYourFaja, Sep 26 '12 at 11:52

score 26 · Accepted Answer · answered Sep 26 '12 at 11:58

26

UPDATE
    animal_attribute_values aav
SET
    animal_attribute_value_name = 'Some new value'
FROM animals aa
WHERE aa.animal_id = 458
AND aa.animal_attrib_value_id = aav.animal_attrib_value_id
  ;

answered Sep 26 '12 at 11:58

wildplasser

43,142
8
66
109

solaimuruganv · Answer 2 · 2012-09-26T12:01:18.337

4

are you asking something like this right..?

update  animal_attribute_values aav
set  aav.animal_attribute_value_name = 'Some new value'
where aav.animal_attrib_value_id in (
select a.animal_attrib_value_id where a.animal_id=458)

try this..

edited Sep 26 '12 at 12:01

answered Sep 26 '12 at 11:51

solaimuruganv

27,177
1
18
23

thanks (+1) - please see the example in my edit. Can you confirm that your code above accomplishes what I'm looking for in my example? Thanks again! – IAmYourFaja Sep 26 '12 at 11:54

Postgres update with an inner join across 2 tables?

2 Answers2

Linked