Update with view parameters

Question

Good morning. I created a view to pick up concatenated data from multiple columns.

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `canal` AS
    SELECT 
        `a`.`id_campaign` AS `id_campaign`,
        `a`.`state` AS `state`,
        CONCAT('id_dialing_list_number_',
                `b`.`id_dialing_list`) AS `Num_Lista`
    FROM
        (`campaign` `a`
        JOIN `campaign_dialing_list` `b` ON (`a`.`id_campaign` = `b`.`id_campaign`))
    WHERE
        `a`.`state` = '3'

And give me back that data:

d_campaign,state,Num_Lista
100,3,id_dialing_list_number_74
100,3,id_dialing_list_number_186
209,3,id_dialing_list_number_185
209,3,id_dialing_list_number_115
211,3,id_dialing_list_number_237
213,3,id_dialing_list_number_68
210,3,id_dialing_list_number_89
214,3,id_dialing_list_number_354
212,3,id_dialing_list_number_156
217,3,id_dialing_list_number_163

I’d like to take from the column Num_lista and update these respective tables.

Someone could give me a light?.

Grateful.

Answer 1

Follow a solution option:

One way to do what you need is by using the following execution:

INSERT INTO nome_da_tabela (nome_da_coluna)
SELECT nome_do_campo_da_view FROM nome_da_view;

This way you will, as seen in the first row, insert in the said column and table, the content obtained from the View.

In this case, it is important to be careful with the primary keys, not to insert them again in the same table and with the data types, not to insert for example, a varchar field of the View in an integer field of the table.

Big hug.