0

I have created a table where it stores some multilingual store data with this structure

create table store_locator_store_lang
(
    id_store int(11) unsigned not null,
    lang_id int not null,
    partner varchar(100) null,
    address varchar(255) null,
    city varchar(100) null,
    occupation varchar(100) null,
    primary key (id_store, id_lang)
);

id_lang can be 1 or 2

In there I have some incomplete data meaning that there are all the rows for lang_id = 1 but for lang_id = 2 many rows contain empty values.

I would like to populate the empty rows for lang_id = 2 with data from the rows with lang_id = 1.

The store entity is identified by id_store so the id_store is common for the two languages of the same store.

There is always a pair stored even if one row has empty columns. There is no chance there is a row missing for lang_id=2

Is it possible to accomplish this task with an update query in the database or do I have to export the data, edit and reinsert?

The Database server is 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Here's a dump of some example data

INSERT INTO store_locator_store_lang (id_store, lang_id, partner, address, city, occupation) VALUES (543, 1, 'TEST', 'TEST, Τ.Κ. 52100', 'TEST', 'TEST');
INSERT INTO store_locator_store_lang (id_store, lang_id, partner, address, city, occupation) VALUES (543, 2, '', '', '', '');
  • Show an example of your data for regular and for problematic pair. Do you want to copy the values from lang_id = 1 record to according lang_id = 2 record where the value is empty? What is empty - NULL or zero-length string? – Akina Feb 20 at 11:05
  • I updated the question with a sample data export. The empty value seems to be zero-length string and not null. – gabtzi Feb 20 at 11:19
1

In general (if "many rows contain empty values" means "they contains NULL"):

UPDATE store_locator_store_lang t1
  JOIN store_locator_store_lang t2 ON t1.id_store = t2.id_store 
                                  AND t1.lang_id = 1
                                  AND t2.lang_id = 2
SET t2.partner = COALESCE(t2.partner, t1.partner),
    t2.address = COALESCE(t2.address, t1.address),
    t2.city = COALESCE(t2.city, t1.city),
    t2.occupation = COALESCE(t2.occupation, t1.occupation);

The empty value seems to be zero-length string and not null.

UPDATE store_locator_store_lang t1
  JOIN store_locator_store_lang t2 ON t1.id_store = t2.id_store 
                                  AND t1.lang_id = 1
                                  AND t2.lang_id = 2
SET t2.partner = CASE WHEN t2.partner = ''
                      THEN t1.partner
                      ELSE t2.partner END,
    t2.address = CASE WHEN t2.address = ''
                      THEN t1.address 
                      ELSE t2.address END,
    t2.city = CASE WHEN t2.city = ''
                   THEN t1.city 
                   ELSE t2.city END,
    t2.occupation = CASE WHEN t2.occupation = ''
                         THEN t1.occupation 
                         ELSE t2.occupation END
/* WHERE '' IN (t2.partner, t2.address, t2.city, t2.occupation) */
    ;
| improve this answer | |
  • I'm reading about COALESCE to understand the query better. – gabtzi Feb 20 at 11:20
  • COALESCE won't work in my case because I'm not dealing with nulls it seems. I tested and it adds empty value again – gabtzi Feb 20 at 11:36
  • @gabtzi Updated according to new info. – Akina Feb 20 at 11:45
  • That's great thank you very much for your help :) I can definitely work with this to fit my needs. – gabtzi Feb 20 at 11:49

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.