I have two tables tbl_lang and tbl_unicode as described below:


lang_id   lang_name   lang_code
1         Français    fr
2         العربية     ar
3         Deutsche    ge


 ent_id   ent_name        fr                ar             ge
  1        lbl_username  Nom d'utilisateur   اسم المستخدم  Benutzername
  2        lbl_password  mot de passe        كلمه السر     Passwort

While displaying in the Datagridview, i am displaying the Language Names (lang_name from tbl_lang) and when i have to run update query on any translations in the datagridview, i want to dynamically get the column name (lang_code from tbl_lang) . Something close to this:

update tbl_unicode set (select lang_code  from tbl_lang where lang_name = 'Français') = "new_value" where ent_id=1  

Can anyone help me on this please.

You can create stored procedure like this:

DROP PROCEDURE IF EXISTS `dinamic_update`$$
CREATE PROCEDURE `dinamic_update`(
    IN p_lang_name VARCHAR(30),
    IN p_ent_id  INT,
    IN p_new_value VARCHAR(30)
SELECT `lang_code` INTO @v_lang_code 
    FROM `tbl_lang` WHERE `lang_name` = p_lang_name;
SET @sql_text = CONCAT('UPDATE `tbl_unicode` SET '
    , @v_lang_code, ' = \'', p_new_value
    , '\' WHERE `ent_id` = ', p_ent_id);
    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;
END; $$

and then call it e.g.

CALL `dinamic_update`('Français', 1, 'new value for Français');
CALL `dinamic_update`('Deutsche', 1, 'new value for Deutsche');
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.