Select data from a table to insert/update another similar table

All we need is an easy explanation of the problem, so here it is.

I have two tables (in two databases):

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
)

My _text table contains some data that I would like to transfer to the _varchar table, but there’s no correlation with the primary keys. The primary key is just auto increment.

Logically the tables are connected by the set (attribute_id, store_id, entity_id).

I would like to perform two actions:

  1. Select all the values from _text table for specific attribute ids, e.g.

    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;
    

    Now for each row in the above results I’d like to:

  2. If there’s corresponding (attribute_id, store_id, entity_id) in the _varchar table, update that row.

  3. If there’s no corresponding (attribute_id, store_id, entity_id) in the _varchar table, add a new row with the data from the source row.

If I had a common primary key, I’d use REPLACE INTO, but in this case I don’t think it’s suitable.

How can I build such a query? It doesn’t need to be a single query.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Here’s how to update the records that exist in catalog_product_entity_varchar and relate to a record in 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;

After you do the update, then you can insert what’s missing like this:

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)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply