UPDATE on子句中的未知列

Question

columnsales_mode value: A|B ->1 | 2 .

我写sql如下:

select GROUP_CONCAT(t.sales_mode SEPARATOR '|')
from (select c.voc_code sales_mode
from rv_p_ab_ass_equ a
JOIN mysql.help_topic b
join rv_pub_voc_transform c
ON b.help_topic_id < (length(a.sales_mode) - length(REPLACE(a.sales_mode, '|', '')) + 1) and
c.voc_code_e = substring_index(substring_index(a.sales_mode, '|', b.help_topic_id + 1), '|', -1)
where c.voc_type_e = 'T_S_DT_TD_MODE'
and c.system_name_e = 'WBXT_YSS_GZ45'
and c.delete_flag = 0) t

正确。

然后我想更新sales_mode列，如下所示:

update rv_p_ab_ass_equ p
set p.sales_mode =
(select GROUP_CONCAT(t.sales_mode SEPARATOR '|')
from (select c.voc_code sales_mode
from  mysql.help_topic b
join rv_pub_voc_transform c
ON b.help_topic_id <
(length(p.sales_mode) - length(REPLACE(p.sales_mode, '|', '')) + 1) and
c.voc_code_e =
substring_index(substring_index(p.sales_mode, '|', b.help_topic_id + 1), '|', -1)
where c.voc_type_e = 'T_S_DT_TD_MODE'
and c.system_name_e = 'WBXT_YSS_GZ45'
and c.delete_flag = 0) t);

错误:[42S22][1054]未知列'p.sales_mode'在'on子句'

思考后:添加图层可以执行如下命令:

update rv_p_ab_ass_equ p
set sales_mode = case
when p.sales_mode is null or p.sales_mode = ' ' then p.sales_mode
else (select n.sales_mode
from (select t.id, GROUP_CONCAT(t.sales_mode SEPARATOR '|') sales_mode
from (select a.id, c.voc_code sales_mode
from rv_p_ab_ass_equ a
JOIN mysql.help_topic b
join rv_pub_voc_transform c
ON b.help_topic_id <
(length(a.sales_mode) - length(REPLACE(a.sales_mode, '|', '')) + 1) and
c.voc_code_e =
substring_index(
substring_index(a.sales_mode, '|', b.help_topic_id + 1),
'|', -1)
where c.voc_type_e = 'T_S_DT_TD_MODE'
and c.system_name_e = '${system_name}'
and c.delete_flag = 0) t
group by t.id) n
where n.id = p.id) end

如何优化?

Answer 1

首先，为这种情况选择NULL或' '。拥有OR几乎是一种普遍的减速。由于单个空格是有问题的，特别是在VARCHARs中，我将为您选择NULL。

update rv_p_ab_ass_equ p
set sales_mode = case
when p.sales_mode is null or p.sales_mode = ' ' then p.sales_mode
...

——比;(步骤2)

-- Get rid of space:
UPDATE rv_p_ab_ass_equ p
SET   sales_mode = NULL
WHERE sales_mode = ' ';
UPDATE rv_p_ab_ass_equ p
SET   sales_mode = ( SELECT ... )
WHERE sales_mode IS NOT NULL

使用部分列的开销很大，特别是在ON或WHERE中使用时。(我指的是substring_index, length, replace。)我建议您在存储到表中之前将内容分成块。这可能涉及使用多行而不是单个行来使用group_concat。

在所有这些之后，可能还有更多的工作可以在第二个UPDATE上完成。