Sql 如何使子查询在UPDATE语句期间获得最新更新

发布于10月05日

我有两张桌子，organization张和address张.

organization表有address_id列，但所有行的address_id都设置为NULL.

我想将组织表中的address_id设置为地址表中的随机ID，但我也希望所有ID都是唯一的.

这就是我try 过的:

UPDATE organization
SET address_id = (SELECT id
                  FROM address
                  WHERE id NOT IN (SELECT address_id FROM organization WHERE address_id IS NOT NULL)
                  LIMIT 1)

但是，子查询不会在每次更新后获得更新值.

What I'm expecting/trying to do:
Assuming on the first update sets address_id to 6, then on the next update 6 should not be returned since 1 organization already has the address_id 6.

But all rows in organization end up having the same address_id.
How can I go about this?

推荐答案

此查询将每个的下一个空闲地址ID分配给还没有空闲地址ID的每个组织:

UPDATE organization
SET    address_id = a1.id
FROM  (
   SELECT a.id, row_number() OVER () AS link_id  -- arbitrary number (not strictly random)
   FROM   address a
   WHERE  NOT EXISTS (SELECT FROM organization o WHERE o.address_id = a.id)
   ) a1
JOIN (
   SELECT o.id, row_number() OVER () AS link_id  -- arbitrary number (not strictly random)
   FROM   organization o
   WHERE  address_id IS NULL
   ) o1 USING (link_id)
WHERE  o1.id = o.id;

Prepare the assignments in subqueries in the FROM clause to the UPDATE.
Read the manual on UPDATE here.

假设没有并发写入访问.否则，您必须锁定这两个表以确保安全.

如果没有足够的空闲地址，一些组织会保留address_id IS NULL个.