Process Multiple Row by a MYSQL Store Procedure

Question

I am facing some trouble inserting data into a table by Store Procedure. Please allow me to explain in details.

I have a Three tables

CREATE TABLE `table_1` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `s_id` INT(11) NOT NULL,
    `created` DATE NOT NULL,    
    `val` VARCHAR(255) NOT NULL,    
    PRIMARY KEY (`id`)

)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;

 CREATE TABLE `table_2` (
    `s_id` INT(11) NOT NULL AUTO_INCREMENT,
    `t_id` INT(11) NOT NULL,
    `p_id` INT(11) NOT NULL,
    `d_id` INT(11) NOT NULL,
    `created` DATE NOT NULL,    

    PRIMARY KEY (`id`)

)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;

CREATE TABLE `table_3` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `count` INT(11) NOT NULL,
    `created` DATE NOT NULL,    
    `s_id` VARCHAR(255) NOT NULL,
    `t_id` VARCHAR(255) NOT NULL,   
    `p_id` VARCHAR(255) NOT NULL,   
    PRIMARY KEY (`id`)

)

Is there any way to insert into 2nd table by store procedure using select statement. There will be multiple data as Select * from table_1; Then there will be a process like if(table_1.s_id == table_3.s_id) THEN UPDATE table_3 set count = count +1 ELSE INSERT into table_3 (s_id,t_id.....

Though I have tried using Cursor but its inserting only one record.

Here are the example of my usage of Cursor

DECLARE done INT DEFAULT FALSE; 
DECLARE curs_count INT(11) DEFAULT 0;

DECLARE v_s_id BIGINT(20) DEFAULT 0;
DECLARE v_t_id BIGINT(20) DEFAULT 0;
DECLARE v_p_id BIGINT(20) DEFAULT 0;
DECLARE v_t_date BIGINT(20) DEFAULT 0;
DECLARE co_s_id BIGINT(20) DEFAULT 0;

DECLARE curs CURSOR FOR
SELECT 
    a.id,b.s_id,b.t_id,b.created
    FROM 
        table_1 a 
        INNER JOIN table_2 b ON a.s_id =  b.s_id        
    WHERE 
        a.val <> '';        
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE curs_id INT(11) DEFAULT 0;

OPEN curs;
  SELECT FOUND_ROWS() INTO curs_count; 
 start_loop: loop
     FETCH curs INTO curs_id,v_s_id,v_t_id,v_c_date;    
     IF done THEN
     LEAVE start_loop;
     END IF;

     SELECT s_id INTO co_s_id FROM table_3;

     IF co_s_id>0
     THEN
     update table_3 SET count = count+1 where s_id = co_s_id;
     ELSE
     INSERT INTO table_3 (support_id,track_id,track_date,count) VALUES (v_s_id,v_t_id,v_c_date,1);
     END IF;
    end loop;
    CLOSE curs;

Can anyone help me out with this?

it can be done without SP... simply use `INSERT INTO ... SELECT ...` syntax see [manual](https://dev.mysql.com/doc/refman/8.0/en/insert-select.html) — Raymond Nijland, Jun 14 '19 at 10:57
Thanks for your comment Raymond. Though I could use client script language for processing this request but before this process some other processes are also running by a Store Procedure so I have no choice to run a select all record in SP. But as I am not so much familiar with Store Procedure so I am unable to process each row record after Select statement "SELECT * FROM table_1"; There is a process of either update table 3 or insert into table 3 according to the condition. Actually I need a query in SP — Jhilom, Jun 14 '19 at 11:02
I am surprised you don't have a forever loop here since you never check done. — P.Salmon, Jun 14 '19 at 11:37
This is an example code actually. I think I am not handling the loop properly as I got infinite result but actually I want to access all the 122 results in table_1. I don't know how to getch all 122 records in a single query and then process by SP. — Jhilom, Jun 14 '19 at 11:47
I got the solutions P.Salmon. Thanks for the help Though I found this code was mine was working actually. But somehow I the tables were corrupted because of my previous infinite loop. — Jhilom, Jun 17 '19 at 05:43

score 0 · Answer 1 · answered Jun 17 '19 at 05:45

0

This code is running properly. The issue was infinite loop made the tables corrupted so no data were inserting.

answered Jun 17 '19 at 05:45

Jhilom

1,028
2
15
33

Process Multiple Row by a MYSQL Store Procedure

1 Answers1