UPDATE unknown column in on clause

49
October 20, 2021, at 08:30 AM

column sales_mode value : A|B -> 1|2.

I write the sql as follows:

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

This is correct.

Then I want to update the sales_mode column as follow:

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);

The error: [42S22][1054] Unknown column 'p.sales_mode' in 'on clause'

After think about: Add a layer can excute as follows:

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

How can I optimize?

Answer 1

First, pick either NULL or ' ' for that case. Having OR is almost universally a slowdown. Since a single space is problematic, especially in VARCHARs, I will pick NULL for you.

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 steps)

-- 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

Using parts of columns is costly, especially when used in ON or WHERE. (I am referring to substring_index, length, replace.) I recommend you split things into pieces before storing into the table. This may involve using multiple rows instead of a single row with a group_concat.

After all that, there may be more work that can be done on the second UPDATE.

READ ALSO
How do I have both &#39;Scoll Into View&#39; and &#39;2D/3D Transform&#39; when pressing on an element?

How do I have both 'Scoll Into View' and '2D/3D Transform' when pressing on an element?

Currently I have both scrolled into view and move & scale set on my elements and the scrolled into view overrode my move & scale transformsHow do I have both on my elements? When I remove scrolled into view, the move & scale worked perfectly...

45
login failed with php oop with ajax

login failed with php oop with ajax

I have done login page using php oops with ajax for signin buttonif(isset($result["username"])!="") Its return wrong details in Json type

40
Getting Empty File with AWS Lambda and ALB

Getting Empty File with AWS Lambda and ALB

I have used the Npm package, http-proxy-middleware

58