主页 / dba / 问题 / 291693
Accepted
gabtzi
Asked: 2021-05-18 00:24:40 +0800 CST

从表中选择数据以插入/更新另一个类似的表

  • 772

我有两个表（在两个数据库中）：

create table catalog_product_entity_text
(
    value_id int auto_increment comment 'Value ID'
        primary key,
    attribute_id smallint unsigned default 0 not null comment 'Attribute ID',
    store_id smallint unsigned default 0 not null comment 'Store ID',
    entity_id int unsigned default 0 not null comment 'Entity ID',
    value longtext null comment 'Value',
    constraint CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
        unique (entity_id, attribute_id, store_id),
    constraint CATALOG_PRODUCT_ENTITY_TEXT_STORE_ID_STORE_STORE_ID
        foreign key (store_id) references store (store_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_TEXT_ATTR_ID_EAV_ATTR_ATTR_ID
        foreign key (attribute_id) references eav_attribute (attribute_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_TEXT_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references catalog_product_entity (entity_id)
            on delete cascade
)

create table catalog_product_entity_varchar
(
    value_id int auto_increment comment 'Value ID'
        primary key,
    attribute_id smallint unsigned default 0 not null comment 'Attribute ID',
    store_id smallint unsigned default 0 not null comment 'Store ID',
    entity_id int unsigned default 0 not null comment 'Entity ID',
    value varchar(255) null comment 'Value',
    constraint CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
        unique (entity_id, attribute_id, store_id),
    constraint CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID_STORE_STORE_ID
        foreign key (store_id) references store (store_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID
        foreign key (attribute_id) references eav_attribute (attribute_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_VCHR_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references catalog_product_entity (entity_id)
            on delete cascade
)

我的_text表包含一些我想传输到_varchar表中的数据，但与主键没有关联。主键只是自动递增。

从逻辑上讲，这些表是由集合连接的(attribute_id, store_id, entity_id)。

我想执行两个操作：

  1. 从_text表中选择特定属性 ID 的所有值，例如

    select cpet.attribute_id, cpet.store_id, cpet.entity_id, cpet.value
from catalog_product_entity_text cpet
where cpet.attribute_id in
      (<myids>)
  and cpet.value is not null;

    现在对于上述结果中的每一行，我想：

  2. 如果表中有对应(attribute_id, store_id, entity_id)的_varchar，则更新该行。

  3. 如果表中没有对应项(attribute_id, store_id, entity_id)，请_varchar使用源行中的数据添加一个新行。

如果我有一个共同的主键，我会使用REPLACE INTO，但在这种情况下，我认为它不合适。

如何构建这样的查询？它不需要是单个查询。

mariadb mariadb-10.4

1 个回答

  1. Best Answer

    以下是如何更新存在于 中的记录catalog_product_entity_varchar并与 中的记录相关的方法catalog_product_entity_text：

    UPDATE catalog_product_entity_varchar cpev
INNER JOIN catalog_product_entity_text cpet
    ON cpet.attribute_id = cpev.attribute_id
    AND cpet.store_id = cpev.store_id
    AND cpet.entity_id = cpev.entity_id
SET cpev.value = cpet.value 
WHERE cpet.attribute_id IN (<myids>)
  AND cpet.value IS NOT NULL;

    完成更新后，您可以像这样插入缺少的内容：

    INSERT INTO catalog_product_entity_varchar (attribute_id, store_id, entity_id, value)
SELECT cpet.attribute_id, cpet.store_id, cpet.entity_id, cpet.value
FROM catalog_product_entity_text cpet
LEFT JOIN catalog_product_entity_varchar cpev
    ON cpet.attribute_id = cpev.attribute_id
    AND cpet.store_id = cpev.store_id
    AND cpet.entity_id = cpev.entity_id
WHERE cpet.attribute_id IN (<myids>)
  AND cpet.value IS NOT NULL
  AND cpev.attribute_id IS NULL; -- This last predicate ensures we only insert new records (i.e. the join didn't match any records in cpev to this record in cpet, if cpev.attribute_id is null here)
    • 1

相关问题