mysql - 从 tableB 插入 tableA where 条件集 case

Question

1st question:

我试图将表 B 中的字段插入到表 A 中，其中我有一个条件并为列 A 设置了一个案例，但它不起作用。是否可以在不更新的情况下做到这一点？

insert into tableA
select date, columnA 
from tableB
where tableB.date between '2015-10-01' and '2015-10-31'
set columnA = case
              when columnA like ('%aaa%') then 'aaa'
              when columnA like ('%bbb%') then 'bbb'
              when columnA like ('%ccc%') then 'ccc'
              when columnA like ('%ddd%') then 'ddd'
              when columnA like ('%eee%') then 'eee'
              else columnA 
              end
;

wchiquito回复并且有效。

2nd question:

此外，我想查看第一个问题的解决方案，以防我还想按日期和 A 列进行分组。

我能做到:

insert into tableA
select date, case
              when columnA like ('%aaa%') then 'aaa'
              when columnA like ('%bbb%') then 'bbb'
              when columnA like ('%ccc%') then 'ccc'
              when columnA like ('%ddd%') then 'ddd'
              when columnA like ('%eee%') then 'eee'
              else columnA 
              end
from tableB
where tableB.date between '2015-10-01' and '2015-10-31'
group by date, case
              when columnA like ('%aaa%') then 'aaa'
              when columnA like ('%bbb%') then 'bbb'
              when columnA like ('%ccc%') then 'ccc'
              when columnA like ('%ddd%') then 'ddd'
              when columnA like ('%eee%') then 'eee'
              else columnA 
              end
;

但是我想看看是否还有其他方法。避免重复编写案例的更好方法。

Answer 1

请记住，column 是保留字，请参阅 9.3 Keywords and Reserved Words .

参见，12.4 Control Flow Functions - CASE .

INSERT INTO `tableA`
SELECT `date`, CASE
                WHEN `column` LIKE '%aaa%' THEN 'aaa'
                WHEN `column` LIKE '%bbb%' THEN 'bbb'
                WHEN `column` LIKE '%ccc%' THEN 'ccc'
                WHEN `column` LIKE '%ddd%' THEN 'ddd'
                WHEN `column` LIKE '%eee%' THEN 'eee'
                ELSE `column`
              END
FROM `tableB`
WHERE `tableB`.`date` BETWEEN '2015-01-01' AND '2015-01-04';

SQL Fiddle demo