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.
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
;