sql - Postgres - 将列移动到另一个表或更新另一列

Question

我有两个表:

PRODUCT >> for the basic item info. Title, time created etc ...

PRODUCT_IMAGE >> for item images. 1 product might have multiple images

PRODUCT_IMAGE 中的“product_id”是 PRODUCT 中“id”的外键

产品

id  |   time    |   title   |   image_url
562 |   2019    |   iPhone  |   http://repo.coi/img/3520.png
....

PRODUCT_IMAGE

id  | product_id    |   url             |   is_primary
10  | 4523          |   /ipo/2388/png   | True
....

我需要将 image_url 列从 PRODUCT 移动到 PRODUCT_IMAGE

IF url already exists in PRODUCT_IMAGE then update is_primary to True

IF NOT then insert new row (product_id, image_url, is_primary=False) to PRODUCT_IMAGE

Answer 1

这需要一个UPDATE:

update PRODUCT_IMAGE pi
set is_primary = True
from PRODUCT p
where p.id = pi.product_id and p.image_url = pi.url;

然后是INSERT:

insert into PRODUCT_IMAGE (product_id, url, is_primary)
select p.id, p.image_url, False
from PRODUCT p
where not exists (
  select 1 from PRODUCT_IMAGE
  where product_id = p.id and url = p.image_url
);

参见 demo .