Postgres 使用跨 2 个表的内部连接进行更新? [英] Postgres update with an inner join across 2 tables?

问题描述

我的本​​地 Postgres 数据库中有 3 个表:

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

在运行时我会知道 animal_id.我需要运行 SQL 来更新与此项目关联的 animal_attribute_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???

我可能需要在 WHERE 子句中执行某种嵌套的 SELECT 或 INNER JOIN，但不确定如何执行此操作.提前致谢！

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!

编辑:

假设我有一个具有以下值的 animal 记录:

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

而对应的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.'

在运行时，我只有 animal_id (458).我需要查找相应的 animal_attrib_value (23) 并将其 animal_attrib_value_name 更改为 'Some new value'，所有这些都在单个 UPDATE 语句中.

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
  ;

这篇关于Postgres 使用跨 2 个表的内部连接进行更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！