时间戳

首页 > 解决方案 > 从另一个表中插入值并使用返回值更新原始表

sql - 从另一个表中插入值并使用返回值更新原始表

问题描述

我是 PostgreSQL（甚至是 Stackoverflow）的新手。
说，我有两张桌子Order和Delivery：

Order

id     product      address           delivery_id
--------------------------------------------------
1      apple        mac street        (null)
3      coffee       java island       (null)
4      window       micro street      (null)

Delivery

id     address
----------------

Delivery.id并且Order.id是自动递增的串行列。
该表Delivery目前是空的。

我想移动Order.address到Delivery.address并Delivery.id到达Order.delivery_id这个状态：

Order

id     product      address           delivery_id
--------------------------------------------------
1      apple        mac street        1
5      coffee       java island       2
7      window       micro street      3

Delivery

id     address
---------------------
1      mac street
2      java island
3      micro street

然后我将删除Order.address.

我为 Oracle 找到了一个类似的问题，但未能将其转换为 PostgreSQL：

我仍然认为应该可以在 Postgres中使用带有RETURNING子句和后续的普通 SQL 语句。INSERT

我试过这个（以及一些变体）：

WITH ids AS (
    INSERT INTO Delivery (address)
    SELECT address
    FROM Order
    RETURNING Delivery.id AS d_id, Order.id AS o_id
)
UPDATE Order
SET Delivery_id = d_id
FROM ids
WHERE Order.id = ids.o_id;

最近的尝试失败了：

错误：缺少表“Delivery”第 1 行的 FROM 子句条目：...address Order RETURNING Delivery.id...

如何正确执行此操作？

标签： sqlpostgresqlcommon-table-expressionsql-returning

解决方案

首先，ORDER是一个保留字。不要将其用作标识符。假设orders为 table nae。

WITH ids AS (
   INSERT INTO delivery (address)
   SELECT DISTINCT address
   FROM   orders
   ORDER  BY address -- optional
   RETURNING *
   )
UPDATE orders o
SET    delivery_id = i.id
FROM   ids i
WHERE  o.address = i.address;

您必须考虑order.address. SELECT DISTINCT产生唯一的地址。

在外部UPDATE，我们现在可以重新加入，address因为delivery.address它是独一无二的。您可能应该在此语句之外保持这种方式，并在列上添加一个UNIQUE约束。

delivery有效地导致和之间的一对多关系orders。中的一行delivery可以有很多对应的行orders。考虑通过相应地添加FOREIGN KEY约束来强制执行。

此语句具有从空delivery表开始的好处。如果delivery不是空的，我们将不得不使用 UPSERT 而不是INSERT. 看：

有关的：

关于您收到的错误消息的原因：

如果可以，请仅使用合法的小写标识符。看：

推荐阅读