0

This is my update query :

Update fa set dynamicl52 = 0 
where catno in (0,1,2,3,4,5,6,7,8,9,10,11) and year in (201605,201606); 

Update fa set dynamicl26 = 0 
where catno in (0,1,2,3,4,5,6,7,8,9,10,11) and year in (201611,201512);

Update fa set dynamic_l13_weeks_flag = 0 
where catno in (0,1,2,3,4,5,6,7,8,9,10,11) and year in (201702,201703);

Update fa set dynamiclm1 = 0 
where catno in (0,1,2,3,4,5,6,7,8,9,10,11) and year in (201704,201705); 

I'm new to Mysql programming, can anyone help me in designing this update process in a Stored Procedure.

My table looks like this:

CREATE TABLE fa_nls_o (
MKT varchar(100) DEFAULT NULL,
PROD varchar(1000) DEFAULT NULL,
PRODUCT_LEVEL varchar(100) DEFAULT NULL,
CUSTOM_MONTH varchar(50) DEFAULT NULL,
CUSTOM_QTR varchar(50) DEFAULT NULL,
DYNAMIC_LM1_FLAG int(10) DEFAULT NULL,
DYNAMIC_L13_WEEKS_FLAG int(10) DEFAULT NULL,
DYNAMIC_L26_WEEKS_FLAG int(10) DEFAULT NULL,
DYNAMIC_L52_WEEKS_FLAG int(10) DEFAULT NULL,
DYNAMIC_YTD_2016_FLAG int(10) DEFAULT NULL,
DYNAMIC_YTD_2017_FLAG int(10) DEFAULT NULL,
DYNAMIC_FY_2015_FLAG int(10) DEFAULT NULL,
DYNAMIC_FY_2016_FLAG int(10) DEFAULT NULL,
DYNAMIC_FY_2014_FLAG int(10) DEFAULT NULL,
CURRENT_PERIOD_FLAG int(10) DEFAULT NULL,
CHARACTERESTIC_1 varchar(50) DEFAULT NULL,
CHARACTERESTIC_2 varchar(50) DEFAULT NULL,
CHARACTERESTIC_3 varchar(50) DEFAULT NULL,
CHOOSE_METRIC double(50,4) DEFAULT NULL,
PERIOD_YEAR int(50) DEFAULT NULL,
CAT_NO int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (PERIOD_YEAR)
SUBPARTITION BY KEY (CAT_NO)
SUBPARTITIONS 12
(PARTITION p0 VALUES LESS THAN (201401) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (201402) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (201403) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (201404) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (201405) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (201406) ENGINE = InnoDB,
PARTITION p12 VALUES LESS THAN (201407) ENGINE = InnoDB,
PARTITION p14 VALUES LESS THAN (201408) ENGINE = InnoDB,
PARTITION p16 VALUES LESS THAN (201409) ENGINE = InnoDB,
PARTITION p18 VALUES LESS THAN (201410) ENGINE = InnoDB,
PARTITION p20 VALUES LESS THAN (201411) ENGINE = InnoDB,
PARTITION p22 VALUES LESS THAN (201412) ENGINE = InnoDB,
PARTITION p24 VALUES LESS THAN (201501) ENGINE = InnoDB,
PARTITION p26 VALUES LESS THAN (201502) ENGINE = InnoDB,
PARTITION p28 VALUES LESS THAN (201503) ENGINE = InnoDB,
PARTITION p30 VALUES LESS THAN (201504) ENGINE = InnoDB,
PARTITION p32 VALUES LESS THAN (201505) ENGINE = InnoDB,
PARTITION p34 VALUES LESS THAN (201506) ENGINE = InnoDB,
PARTITION p36 VALUES LESS THAN (201507) ENGINE = InnoDB,
PARTITION p38 VALUES LESS THAN (201508) ENGINE = InnoDB,
PARTITION p40 VALUES LESS THAN (201509) ENGINE = InnoDB,
PARTITION p42 VALUES LESS THAN (201510) ENGINE = InnoDB,
PARTITION p44 VALUES LESS THAN (201511) ENGINE = InnoDB,
PARTITION p46 VALUES LESS THAN (201512) ENGINE = InnoDB,
PARTITION p48 VALUES LESS THAN (201601) ENGINE = InnoDB,
PARTITION p50 VALUES LESS THAN (201602) ENGINE = InnoDB,
PARTITION p52 VALUES LESS THAN (201603) ENGINE = InnoDB,
PARTITION p54 VALUES LESS THAN (201604) ENGINE = InnoDB,
PARTITION p56 VALUES LESS THAN (201605) ENGINE = InnoDB,
PARTITION p58 VALUES LESS THAN (201606) ENGINE = InnoDB,
PARTITION p60 VALUES LESS THAN (201607) ENGINE = InnoDB,
PARTITION p62 VALUES LESS THAN (201608) ENGINE = InnoDB,
PARTITION p64 VALUES LESS THAN (201609) ENGINE = InnoDB,
PARTITION p66 VALUES LESS THAN (201610) ENGINE = InnoDB,
PARTITION p68 VALUES LESS THAN (201611) ENGINE = InnoDB,
PARTITION p70 VALUES LESS THAN (201612) ENGINE = InnoDB,
PARTITION p72 VALUES LESS THAN (201701) ENGINE = InnoDB,
PARTITION p74 VALUES LESS THAN (201702) ENGINE = InnoDB,
PARTITION p76 VALUES LESS THAN (201703) ENGINE = InnoDB,
PARTITION p78 VALUES LESS THAN (201704) ENGINE = InnoDB,
PARTITION p80 VALUES LESS THAN (201705) ENGINE = InnoDB,
PARTITION p82 VALUES LESS THAN (201706) ENGINE = InnoDB,
PARTITION p84 VALUES LESS THAN (201707) ENGINE = InnoDB,
PARTITION p86 VALUES LESS THAN (201708) ENGINE = InnoDB,
PARTITION p88 VALUES LESS THAN (201709) ENGINE = InnoDB,
PARTITION p90 VALUES LESS THAN (201710) ENGINE = InnoDB,
PARTITION p92 VALUES LESS THAN (201711) ENGINE = InnoDB,
PARTITION p94 VALUES LESS THAN (201712) ENGINE = InnoDB,
PARTITION p96 VALUES LESS THAN (201801) ENGINE = InnoDB) */

closed as unclear what you're asking by Philᵀᴹ, mustaccio, joanolo, RDFozz, Erik Jul 14 '17 at 19:38

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • what have you tried so far? what are you expecting as the input parameters to your stored proc? – markp Jul 14 '17 at 14:19
1

It might some help you.

-- Sample Data

DROP TABLE new_test.fa;
CREATE TABLE new_test.fa(
id INT PRIMARY KEY AUTO_INCREMENT,
catno INT,
`year` INT,
dynamicl52 INT,
dynamicl26 INT,
dynamic_l13_weeks_flag INT,
dynamiclm1 INT,
KEY (`year`),
KEY (catno));

INSERT INTO new_test.fa(catno,year,dynamicl52,dynamicl26,dynamic_l13_weeks_flag,dynamiclm1) VALUES (1,201605,1,1,1,1);
INSERT INTO new_test.fa(catno,year,dynamicl52,dynamicl26,dynamic_l13_weeks_flag,dynamiclm1) VALUES (2,201606,1,1,1,1);
INSERT INTO new_test.fa(catno,year,dynamicl52,dynamicl26,dynamic_l13_weeks_flag,dynamiclm1) VALUES (3,201611,1,1,1,1);
INSERT INTO new_test.fa(catno,year,dynamicl52,dynamicl26,dynamic_l13_weeks_flag,dynamiclm1) VALUES (4,201512,1,1,1,1);
INSERT INTO new_test.fa(catno,year,dynamicl52,dynamicl26,dynamic_l13_weeks_flag,dynamiclm1) VALUES (5,201702,1,1,1,1);
INSERT INTO new_test.fa(catno,year,dynamicl52,dynamicl26,dynamic_l13_weeks_flag,dynamiclm1) VALUES (6,201703,1,1,1,1);

-- Procedure Call
CALL new_test.sp_update_fa('dynamiclm1','1,2,3','201605');-- Sample Data

DROP TABLE new_test.fa;
CREATE TABLE new_test.fa(
id INT PRIMARY KEY AUTO_INCREMENT,
catno INT,
year INT,
dynamicl52 INT,
dynamicl26 INT,
dynamic_l13_weeks_flag INT,
dynamiclm1 INT,
KEY (year),
KEY (catno));

INSERT INTO new_test.fa(catno,year,dynamicl52,dynamicl26,dynamic_l13_weeks_flag,dynamiclm1) VALUES (1,201605,1,1,1,1);
INSERT INTO new_test.fa(catno,year,dynamicl52,dynamicl26,dynamic_l13_weeks_flag,dynamiclm1) VALUES (2,201606,1,1,1,1);
INSERT INTO new_test.fa(catno,year,dynamicl52,dynamicl26,dynamic_l13_weeks_flag,dynamiclm1) VALUES (3,201611,1,1,1,1);
INSERT INTO new_test.fa(catno,year,dynamicl52,dynamicl26,dynamic_l13_weeks_flag,dynamiclm1) VALUES (4,201512,1,1,1,1);
INSERT INTO new_test.fa(catno,year,dynamicl52,dynamicl26,dynamic_l13_weeks_flag,dynamiclm1) VALUES (5,201702,1,1,1,1);
INSERT INTO new_test.fa(catno,year,dynamicl52,dynamicl26,dynamic_l13_weeks_flag,dynamiclm1) VALUES (6,201703,1,1,1,1);

--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS new_test.sp_update_fa;
DELIMITER $$

CREATE PROCEDURE new_test.sp_update_fa(p_Col VARCHAR(64),p_catno 
VARCHAR(1000),p_year VARCHAR(1000))

main:BEGIN
    DECLARE stat,v_Col VARCHAR(10000);
    DECLARE v_catno,v_year INT;

    SELECT c.column_name INTO v_Col
        FROM information_schema.columns c 
        WHERE c.table_schema='new_test' -- Change DB_name
            AND c.table_name='fa' -- Change_Table_Name
            AND c.column_name=p_Col;

    SELECT 
        IF(p_catno LIKE ',%',1,0)+ -- check if no , in front in p_catno
        IF(p_catno LIKE '%,',1,0)+ -- check if no , in end in p_catno
        IF((REPLACE(p_catno,',','') REGEXP '^-?[0-9]+$')=1,0,1) -- Check if values are numeric p_catno
    INTO v_catno ;

    SELECT 
        IF(p_year LIKE ',%',1,0)+ -- check if no , in front in p_year
        IF(p_year LIKE '%,',1,0)+ -- check if no , in end in p_year
        IF((REPLACE(p_year,',','') REGEXP '^-?[0-9]+$')=1,0,1) -- Check if values are numeric p_year
    INTO v_year ;

    #Validate Input Column Name
    IF p_Col IS NULL OR p_Col='' OR v_Col IS NULL OR v_Col='' OR v_Col<>p_Col THEN
        SELECT 'Wrong Input Column Name' AS Output;
        LEAVE main;
    END IF;

    #Validate Input catno
    IF p_catno IS NULL OR p_catno='' OR v_catno<>0 THEN
        SELECT 'Wrong String passed for "catno"' AS Output;
        LEAVE main;
    END IF;

    #Validate Input year
    IF p_year IS NULL OR p_year='' OR v_year<>0 THEN
        SELECT 'Wrong String passed for "year"' AS Output;
        LEAVE main;
    END IF;


    SET @stmt = CONCAT('Update new_test.fa set ',v_Col,' = 0 where catno in (',p_catno,') and year in (',p_year,');');

    PREPARE pstmt FROM @stmt; 

    -- select @stmt;
    EXECUTE pstmt;

    DEALLOCATE PREPARE pstmt;    

    END$$
DELIMITER ;
-------------------------------------------------------------------------------

-- Procedure Call
CALL new_test.sp_update_fa('dynamiclm1','1,2,3','201605');
  • Instead of index key on both cat no and period year, we have existing table with partition to periodyear and subpartition to cat_no – SAM AB Jul 17 '17 at 8:53
  • Are you facing any error/issue with and without indexes that is defined in sample table definition. – akumar Jul 17 '17 at 12:52
  • i cannot do insert process, there is some 150 million data exist ,please check the orginal table structure,where we need to update th existing data – SAM AB Jul 18 '17 at 9:02
  • Here I mentioned INSERT statements to just describe and populate table on which this procedure will run. Please elaborate the requirement if I am missing something. – akumar Jul 18 '17 at 9:06
2

here below is an example

-- example table with data   
create database  if not exists test ;  
use test;  
drop table if exists test.table1;  
CREATE TABLE if not exists test.table1 (  
  id INT(10) NOT NULL,  
  value INT(10) NOT NULL,  
  PRIMARY KEY (id));  
insert into table1 values (1,1);  
insert into table1 values (2,2);  
insert into table1 values (3,3);  
insert into table1 values (4,4);  
insert into table1 values (5,5);  
insert into table1 values (6,6);  
insert into table1 values (7,7);  
insert into table1 values (1001,1001);  
insert into table1 values (1002,1002);  
insert into table1 values (2002,2002);  
insert into table1 values (6002,6002);  
drop procedure if exists test.my_stored_procedure;  
DELIMITER $$  
CREATE PROCEDURE test.my_stored_procedure(IN idmin INT(10), IN idmax  INT(10))  
BEGIN  
update table1 set value = 999  
where id in (0,1,2) or (id >= idmin and id <= idmax);  
END$$  
DELIMITER;  
-- test  
select * from test.table1;  
call test.my_stored_procedure(1002,2002);  
select * from test.table1;
  • i really confused with your answer, Can you help me on this! – SAM AB Jul 17 '17 at 9:55
1

Not sure why a procedure is needed, you can do this with one update statement:

Update fa 
    set dynamicl52 = 0
      , dynamicl26 = 0 
      , dynamic_l13_weeks_flag = 0 
      , dynamiclm1 = 0 
where catno in (0,1,2,3,4,5,6,7,8,9,10,11) 
  and year in (201605,201606); 
  • we can do it with one updates, But i required stored procedures which can update faster – SAM AB Jul 17 '17 at 9:34
  • What makes you think that a stored procedure is faster? There are two main advantages of stored procedures, the first has to do with access rights (not applicable here), and the second is to avoid round trip time between the application and the database for long transactions (not applicable here) – Lennart Jul 17 '17 at 9:39
  • stored procedure never parse nor optimize the query whereas update process parses and search for optimizing the query . currently the update process taking 6 hours, that is why i tried to for stored routines. If Im wrong ,correct me on this! – SAM AB Jul 17 '17 at 9:58
  • If you look at the parse and compile time for the statement I'm pretty sure that it is a very small part of 6 hours (my guess is seconds). Look at the explain, does it prune the other partitions or does it scan all partitions? – Lennart Jul 17 '17 at 13:28
  • ,it prunes to partitions instead of scan all partitions, but it takes more than hour – SAM AB Jul 18 '17 at 8:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.