Postgres使用2个表之间的内部联接进行更新吗？

编程入门行业动态更新时间:2024-10-23 21:34:55

本文介绍了Postgres使用2个表之间的内部联接进行更新吗？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我在本地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???

我可能必须做某种嵌套的 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!

编辑：

假设我有一个动物记录，其值如下：

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新值，都在单个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 ;