postgresql 在单个查询中更新多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29898244/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgresql update multiple tables in single query
提问by Blip
I have 2 tables as below:
我有 2 个表，如下所示：
serial_table
id CHARACTER VARYING(20), serial_key CHARACTER VARYING(20), PRIMARY KEY(id, serial_key)serial_rate:
id CHARACTER VARYING(20), serial_key CHARACTER VARYING(20), rate NUMERIC, PRIMARY KEY(id, serial_key), FOREIGN KEY (id, serial_key) REFERENCES serial_table(id, serial_key)
序列表
id CHARACTER VARYING(20), serial_key CHARACTER VARYING(20), PRIMARY KEY(id, serial_key)串行速率：
id CHARACTER VARYING(20), serial_key CHARACTER VARYING(20), rate NUMERIC, PRIMARY KEY(id, serial_key), FOREIGN KEY (id, serial_key) REFERENCES serial_table(id, serial_key)
now I want to update serial_rate.rateand serial_table.serial_keyfrom a single SQL Query like :
现在我想从单个 SQL 查询更新serial_rate.rate和更新serial_table.serial_key：
UPDATE inventory.serial_table AS s
JOIN inventory.serial_rate AS r
ON (s.id, s.serial_key) = (r.id, r.serial_key)
SET s.serial_key = '0002', r.rate = 22.53
WHERE (s.id, s.serial_key) = ('01', '002');
Which I know is incorrect. Is there a possible way to do thisas I would like to use the statement to create a PreparedStatementin Java?
我知道这是不正确的。有没有可能的方法来做到这一点，因为我想使用该语句PreparedStatement在 Java 中创建一个？
EDITThis question is not about PreparedStatementsin Java It is about SQL Syntax that I wish to pass as parameter while creating a PreparedStatement. I don't want any answer about PreparedStatement.
编辑这个问题PreparedStatements与 Java无关，而是关于 SQL 语法，我希望在创建PreparedStatement. 我不想要任何关于PreparedStatement.
回答by wildplasser
This is a CTE thing (but I don't know how to wrap it into a prepared Java-thing)
这是一个 CTE 的东西（但我不知道如何将它包装成一个准备好的 Java 东西）
WITH src AS (
UPDATE serial_rate
SET rate = 22.53, serial_key = '0002'
WHERE serial_key = '002' AND id = '01'
RETURNING *
)
UPDATE serial_table dst
SET serial_key = src.serial_key
FROM src
-- WHERE dst.id = src.id AND dst.serial_key = '002'
WHERE dst.id = '01' AND dst.serial_key = '002'
;
回答by Blip
This was what was posted by a_horse_with_no_nameon sqlfiddle.com:
这是a_horse_with_no_name在 sqlfiddle.com 上发布的内容：
WITH id_values (new_key, old_key, id) as (
values ('0002', '002', '01')
), src AS (
UPDATE serial_rate
SET rate = 22.53,
serial_key = (select new_key from id_values)
WHERE serial_key = (select old_key from id_values)
AND id = (select id from id_values)
RETURNING *
)
UPDATE serial_table dst
SET serial_key = src.serial_key
FROM src
WHERE dst.id = (select id from id_values)
AND dst.serial_key = (select old_key from id_values)
;
This is actually a modified version of what was posted by wildplasseras a reply to my query that whether the serial_keyfield could be used once.
这实际上是wildplasser发布的内容的修改版本，作为对我关于该serial_key字段是否可以使用一次的查询的回复。
