CODEWITHSUNDEEP
mysqlvariablessql-updateinner-join
Luc Snoekx
Luc Snoekx

Reputation: 1

MySQL update large table with a variable in SET columname

I have to update a table with SET columname as variable selecting from another table. This is my MySQL :

UPDATE famcharts_old.db1w_matrix
JOIN famcharts_old.tbl_dirtosv
    ON famcharts_old.tbl_dirtosv.PlaceOT = famcharts_old.db1w_matrix.SERVICE_ID
    and famcharts_old.tbl_dirtosv.Dir_ID IN (a LIST-of-values)
JOIN famcharts_old.tbl__dir__base
    ON all_directives.DirID = famcharts_old.tbl_dirtosv.Dir_ID
SET famcharts_old.db1w_matrix.**@variable** = famcharts_old.tbl_dirtosv.NiceNeed;

The @variable is coming from a select where the variable is equal to an Id in the List-of-Values.

I tried several JOINS but I can't get the @variable into the name of the column.

Upvotes: 0

Views: 42

Answers (1)

Ricardo Frasson
Ricardo Frasson

Reputation: 83

try to use this DML command with a prepared statement

    set @variable = 'column_name';

    set @cmdSql = "UPDATE famcharts_old.db1w_matrix 
    JOIN famcharts_old.tbl_dirtosv ON famcharts_old.tbl_dirtosv.PlaceOT = 
    famcharts_old.db1w_matrix.SERVICE_ID
    and famcharts_old.tbl_dirtosv.Dir_ID IN (a LIST-of-values)
    JOIN famcharts_old.tbl__dir__base
    ON all_directives.DirID = famcharts_old.tbl_dirtosv.Dir_ID
    SET famcharts_old.db1w_matrix.";
    
    set @cmdSql=Concat(@cmdSql,@variable);
    
    set @cmdSql=Concat(@cmdSql," = famcharts_old.tbl_dirtosv.NiceNeed; ");
    
    
     Prepare execSQL from @cmdSql;

     Execute execSQL; 

     DEALLOCATE PREPARE execSQL;

More instructions check: https://dev.mysql.com/doc/refman/8.4/en/sql-prepared-statements.html

Upvotes: 0

Related Questions