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
– IAmYourFaja, Commented Sep 26, 2012 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
– wildplasser, Commented Sep 26, 2012 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
– IAmYourFaja, Commented Sep 26, 2012 at 11:40

wildplasser · Accepted Answer · 2012-09-26 11:58:59Z

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, 2012 at 11:58

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

solaimuruganv · Accepted Answer · 2012-09-26 12:01:18Z

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, 2012 at 12:01

answered Sep 26, 2012 at 11:51

solaimuruganv

30.3k1 gold badge22 silver badges23 bronze badges

1 Comment

IAmYourFaja Over a year ago

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!

Collectives™ on Stack Overflow

Postgres update with an inner join across 2 tables?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related