从另一个表递归更新MySQL表

Question

我有两个表，table1和table2，其中table1被更新以根据table2中的匹配字段填充缺失(null)值，以创建更完整的table1。

我尝试过许多查询，例如

UPDATE table1 INNER JOIN table2...SET...

和

UPDATE table1 SET ... (SELECT)...

然而，我的结果总是不完整的。注意，我在两个表中的列和行都有一个更大的数据集)。我只是用这个作为简单的例子。

规则：

1) The `keyword` from table2 looks for a match in `keyword` in table1 and must accept partial matches.
2) No values can be overwritten in table1 (update null values only)
3) The lookup order is per run_order in table2.  

具体例子：

Table1：

|-----|-------------------------------|----------|-----|---------|-------|                  
|t1_id|keyword                        |category  |month|age      |skill  |
|-----|-------------------------------|----------|-----|---------|-------|  
|  1  |childrens-crafts-christmas     |kids      |     |         |       |  
|  2  |kids-costumes                  |          |     |tween    |       |  
|  3  |diy-color-printable            |printable |     |         |easy   |  
|  4  |toddler-halloween-costume-page |          |     |         |       |  
|-----|-------------------------------|----------|-----|---------|-------|

Table2：

|-----|---------|---------|----------|-----|----------|-------|
|t2_id|run_order|keyword  |category  |month|age       |skill  |
|-----|---------|---------|----------|-----|----------|-------|
|  1  |   1     |children |          |     |4-11 yrs  |       |
|  2  |   2     |printable|          |     |          |easy   |
|  3  |   3     |costume  |halloween | 10  |0-12 years|       |
|  4  |   4     |toddler  |          |     |1-3 years |       |
|  5  |   5     |halloween|holiday   | 10  |          |       |
|-----|---------|---------|----------|-----|----------|-------|

结果：

|-----|-------------------------------|----------|-----|---------|-------|                  
|t1_id|keyword                        |category  |month|age      |skill  |
|-----|-------------------------------|----------|-----|---------|-------|  
|  1  |childrens-crafts-christmas     |kids      |     |4-11 yrs |       |  
|  2  |kids-costumes                  |halloween | 10  |tween    |       |  
|  3  |diy-color-printable  printable |printable |     |         |easy   |  
|  4  |toddler-halloween-costume-page |holiday   | 10  |1-3 years|       |  
|-----|-------------------------------|----------|-----|---------|-------|  

用于模式和表数据的MySQL：

DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2; 

CREATE TABLE `table1` (
  `t1_id` INT NOT NULL AUTO_INCREMENT,
  `keyword` VARCHAR(200) NULL,
  `category` VARCHAR(45) NULL,
  `month` VARCHAR(45) NULL,
  `age` VARCHAR(45) NULL,
  `skill` VARCHAR(45) NULL,
  PRIMARY KEY (`t1_id`));

  CREATE TABLE `table2` (
  `t2_id` INT NOT NULL AUTO_INCREMENT,
  `run_order` INT NULL,
  `keyword` VARCHAR(200) NULL,
  `category` VARCHAR(45) NULL,
  `month` INT NULL,
  `age` VARCHAR(45) NULL,
  `skill` VARCHAR(45) NULL,
  PRIMARY KEY (`t2_id`));

INSERT INTO `table1` (`keyword`, `category`) VALUES ('childrens-crafts-christmas', 'kids');
INSERT INTO `table1` (`keyword`, `age`) VALUES ('kids-costumes', 'tween');
INSERT INTO `table1` (`keyword`, `category`, `skill`) VALUES ('diy-color-printable', 'printable', 'easy');
INSERT INTO `table1` (`keyword`) VALUES ('toddler-halloween-costume-page');

INSERT INTO `table2` (`run_order`, `keyword`, `age`) VALUES (1, 'children', '4-11 yrs');
INSERT INTO `table2` (`run_order`, `keyword`, `skill`) VALUES (2, 'printable', 'easy');
INSERT INTO `table2` (`run_order`, `keyword`, `category`, `month`, `age`) VALUES (3, 'costume', 'halloween', 10, '0-12 years');
INSERT INTO `table2` (`run_order`, `keyword`, `age`) VALUES (4, 'toddler', '1-3 years');
INSERT INTO `table2` (`run_order`, `keyword`, `category`, `month`) VALUES (5, 'halloween', 'holiday', 10);

Answer 1

您希望用table1中的第一个匹配记录的对应列( run_order wise )中的值更新run_order中的空值。一个典型的解决方案是使用关联子查询组合来查找table2中的匹配记录，并且只保留run_order最低的记录。

下面是一个使用此逻辑更新null类别的查询：

update table1 t1
set category = (
    select category
    from table2 t2
    where t2.run_order = (
            select min(t22.run_order) 
            from table2 t22 
            where 
                t1.keyword like concat('%', t22.keyword, '%') 
                and t22.category is not null
        )
)
where t1.category is null

这假设run_order在table2中是唯一的(这似乎与您的用例相关)。

您可以用coalesce()扩展更多列的逻辑。下面是列category和month的解决方案

update table1 t1
set 
    category = coalesce(
        t1.category, 
        (
            select category
            from table2 t2
            where t2.run_order = (
                    select min(t22.run_order) 
                    from table2 t22 
                    where 
                        t1.keyword like concat('%', t22.keyword, '%') 
                        and t22.category is not null
                )
        )
    ),
    month = coalesce(
        t1.month, 
        (
            select month
            from table2 t2
            where t2.run_order = (
                    select min(t22.run_order) 
                    from table2 t22 
                    where 
                        t1.keyword like concat('%', t22.keyword, '%') 
                        and t22.month is not null
                )
        )
    )
where t1.category is null or t1.month is null

DB Fiddle演示

Answer 2

您可以使用类似的表达式和if nul()函数在两个表之间使用一个连接，以确保不覆盖非空值。

UPDATE table1 t1
    INNER JOIN table2 t2 ON t1.keyword like concat("%",t2.keyword,"%")
SET 
    t1.category = ifnull(t1.category,t2.category),
    t1.age = ifnull(t1.age,t2.age),
    t1.skill = ifnull(t1.skill,t2.skill);