update afh_products
set mpn = reference_id
where LENGTH(reference_id) = 9 and reference_id REGEXP '^[0-9]+$' = 1 AND mpn = '';

update afh_products
set mpn = gtin
where LENGTH(gtin) = 9 AND (mpn = '' OR mpn = 0);

update afh_products
set gtin = reference_id
where LENGTH(reference_id) = 13 and length(gtin) < 13 and reference_id REGEXP '^[0-9]+$' = 1 and (mpn = gtin OR mpn = 0);

update afh_products
set gtin = NULL
where gtin = mpn AND length(gtin) != 13;

update afh_products
set mpn = NULL
where gtin = mpn AND length(mpn) >= 13;

!!!!! minsan can be 12 ?? sure?

-------

, score REGEXP '^[0-9]+$' AS isNumeric

-------
select distinct mpn, group_concat(distinct gtin)
from afh_products
where length(mpn) = 9 and length(gtin) = 13 and gtin != 0
group by mpn
limit 1000;
#to fill missing values

UPDATE afh_products p 
INNER JOIN (
	select distinct mpn, group_concat(distinct gtin) as gtin
	from afh_products
	where length(mpn) = 9 and length(gtin) = 13 and gtin != 0
	group by mpn
) d ON p.mpn = d.mpn
SET p.gtin = d.gtin
WHERE length(d.gtin) = 13 AND (p.gtin = 0 OR length(p.gtin) < 13 )
;
UPDATE afh_products p 
INNER JOIN (
	select distinct gtin, group_concat(distinct mpn) as mpn
	from afh_products
	where length(mpn) = 9 and length(gtin) = 13 and mpn != 0
	group by gtin
) d ON p.gtin = d.gtin
SET p.mpn = d.mpn
WHERE length(d.mpn) = 9 AND (p.mpn = 0 OR length(p.mpn) != 9 )
;


UPDATE afh_products p 
INNER JOIN (
	select mpn, group_concat(distinct gtin) as gtin, lower(name) as name, count(name)
	from afh_products
	where length(mpn) = 9 and length(gtin) = 13 and gtin != 0 AND length(name) > 20
	group by lower(name)
) d ON LOWER(p.name) = d.name
SET p.gtin = d.gtin
WHERE length(d.gtin) = 13 AND (p.gtin = 0 OR length(p.gtin) < 13 )
;

UPDATE afh_products p 
INNER JOIN (
	select mpn, group_concat(distinct gtin) as gtin, lower(name) as name, count(name)
	from afh_products
	where length(mpn) = 9 and length(gtin) = 13 and gtin != 0 AND length(name) > 20
	group by lower(name)
) d ON LOWER(p.name) = d.name
SET p.mpn = d.mpn
WHERE length(d.mpn) = 9 AND (p.mpn = 0 OR length(p.mpn) != 9 )
;

-----

select * 
from afh_products
where LENGTH(reference_id) = 13 and reference_id REGEXP '^[0-9]+$' = 1 AND gtin = 0
limit 100

select * 
from afh_products
where LENGTH(reference_id) = 13 and reference_id REGEXP '^[0-9]+$' = 1 AND gtin != reference_id
limit 100

select * 
from afh_products
where LENGTH(reference_id) = 9 and reference_id REGEXP '^[0-9]+$' = 1 AND mpn = ''
limit 100

select * 
from afh_products
where LENGTH(reference_id) = 13 and length(gtin) < 13 and reference_id REGEXP '^[0-9]+$' = 1 and mpn = gtin
limit 100

select * 
from afh_products
where LENGTH(gtin) = 9
limit 100

select *, substr(description, 1, 9) as mpndesc
from afh_products
where reference_id REGEXP '^[0-9]+$' = 1 AND substr(description, 1, 9) != mpn
having mpndesc REGEXP '^[0-9]+$' = 1
limit 100