0

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.

0

1 Answer 1

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)
2
  • 1
    Thanks I'll give it a shot. Normally my data resides in two databases but that's not an issue. I can copy one table over to the other db for my needs :)
    – gabtzi
    May 17, 2021 at 11:50
  • No problem! I'm not a MariaDB expert, but I don't think it matters that the data is across databases (if those databases are on the same server), as long as you specify the database name as a prefix on the table name, e.g. Database1.catalog_product_entity_text and Database2.catalog_product_entity_varchar, then you should be able to access your tables across databases without having to copy the data over. Best of luck!
    – J.D.
    May 17, 2021 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.