在单个查询中更新多个表的PostgreSQL方法

Question

我有以下两个表：

1. serial_table

   id CHARACTER VARYING(20),
   serial_key CHARACTER VARYING(20),
   PRIMARY KEY(id, serial_key)
   

2. 串口波特率：

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)

现在我想要通过单个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');

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

编辑 这个问题不是关于Java中的PreparedStatements，而是关于我希望在创建PreparedStatement时将SQL语法作为参数传递。 我不想得到任何关于PreparedStatement的答案。

- Blip

---

你真的打算更新 serial_table.serial_key 这个主键组件吗？另外，是否也应该更新 serial_rate.serial_key（这也是一个主键组件，并且是第一个的外键）？如果不更新的话，可能会违反外键约束。 - wildplasser

@wildplasser 第一个答案是肯定的，至于第二部分，外键是 ON UPDATE CASCADE ON DELETE RESTRICT。 - Blip

可能是Java多个表的一个preparedstatement的重复问题。 - user177800

@JarrodRoberson 我不明白为什么你认为这个问题和你链接的那个问题相似？我在那个链接中没有得到我想要的答案。 - Blip

如果你的问题只涉及SQL语法，我会删除所有“prepared statement”的提及。 - David Aldridge

Answer 1

这是一个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'
        ;

- wildplasser

---

这仍然是两个语句，不像在一个事务/存储过程中执行两个更新语句那样立即清晰，而且将更难以实现为预处理语句（想象一下一个到处都是变量的长丑字符串）。 - Dan Field

不，它们被笨拙地链接在一起。提示：只有一个分号。只需在其前面加上“EXPLAIN”，您就会发现它将导致组合更新的一个查询计划。 - wildplasser

这实际上是一个子查询，对吧？它是两个查询，但只有一个查询计划？ - Dan Field

不，只有一个查询语句，当然也只有一个查询计划。关键是将CTE与在第一个UPDATE命令中使用RETURNING的结合体，这将在第二个UPDATE命令中使用。 - Frank Heikens

@Blip：是的，可以通过查询中的另一个CTE实现：http://sqlfiddle.com/#!15/75387/1 - user330315

Answer 2

这是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)
    ;

这实际上是对wildplasser回答我的问题的修改版本，问题是是否可以只使用serial_key字段一次。

- Blip