我试图在PostgreSQL 9.5.2中编写这样的查询:
INSERT INTO a (id, x)
SELECT id, x FROM b
ON CONFLICT (id) DO UPDATE
SET x = b.x
WHERE b.y < 100
但我得到ERROR: missing FROM-clause entry for table "b"
分.我肯定缺少一些基本的东西,但是我如何引用UPDATE子句中插入的行呢?还是有别的办法?
我试图在PostgreSQL 9.5.2中编写这样的查询:
INSERT INTO a (id, x)
SELECT id, x FROM b
ON CONFLICT (id) DO UPDATE
SET x = b.x
WHERE b.y < 100
但我得到ERROR: missing FROM-clause entry for table "b"
分.我肯定缺少一些基本的东西,但是我如何引用UPDATE子句中插入的行呢?还是有别的办法?
冲突值可通过excluded
别名获得:
INSERT INTO a (id, x)
SELECT id, x
FROM b
ON CONFLICT (id) DO UPDATE
SET x = excluded.x;