PostgreSQL在单个查询中更新多个表 [英] postgresql update multiple tables in single query

问题描述

我有2张桌子，如下所示：

I have 2 tables as below:

1. serial_table

1. 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)

现在我要更新 serial_rate.rate 和 serial_table.serial_key 来自单个SQL查询，例如：

now I want to update serial_rate.rate and serial_table.serial_key from a single SQL Query like :

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');

我知道这是不正确的。 有没有办法做到这一点，因为我想使用该语句在Java中创建 PreparedStatement ？

Which I know is incorrect. Is there a possible way to do this as I would like to use the statement to create a PreparedStatement in Java?

编辑
这个问题与Java中的 PreparedStatements 无关。在创建 PreparedStatement 时作为参数传递。 我不想要任何有关 PreparedStatement 的答案。

EDIT This question is not about PreparedStatements in 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.

推荐答案

这是CTE的东西（但我不知道如何将其包装到准备好的Java东西中）

This is a CTE thing (but I don't know how to wrap it into a prepared Java-thing)

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

这篇关于PostgreSQL在单个查询中更新多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！