How to update table using execute statement?

Question

I have two tables (much simplified here):

QUADRI

ID SBR_750 b10C  TGI
---------------------
Q1    0      1    0
Q2    2      1    0
Q3    1      0    1

CELLE

CELLANAME NEEDED READY
----------------------
SBR_750    NULL   12
b10C       NULL   10
TGI        NULL    5

I want this result in CELLE:

CELLANAME NEEDED READY
------------------------
SBR_750     3     12
b10C        2     10
TGI         1      5

I tried to write a stored procedure but it doesn't works: ERROR 1210. Incorrect arguments to EXECUTE.

Here is the code:

CREATE DEFINER=`root`@`%.zamberlan.local` PROCEDURE `AggiornaCelle`(IN nomecella varchar(15))
BEGIN
set @s='update celle set needed=(select sum(?) from quadri) where cellaname=?';
set @NC=nomecella;
prepare stmt from @s;
execute stmt using @NC;
deallocate prepare stmt;
END

UPDATE: It doesn't work so I change strategy:

CREATE DEFINER=`root`@`%.zamberlan.local` PROCEDURE `AggiornaCelle`()
BEGIN
declare i int;
declare num_rows int;
declare col_name varchar(20);
DECLARE col_names CURSOR FOR
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = quadri
  ORDER BY ordinal_position;


select FOUND_ROWS() into num_rows;

SET i = 1;
the_loop: LOOP

   IF i > num_rows THEN
        CLOSE col_names;
        LEAVE the_loop;
    END IF;


    FETCH col_names 
    INTO col_name;     

     update celle set needed=sum(col_name) where cellaname=col_name;

    SET i = i + 1;  
END LOOP the_loop;
END

inspired by mysql, iterate through column names.

However I receive the error "Cursor is not open..."

what is the logic? Why `SBR_750 = 3`? – Juan Carlos Oropeza Jan 24 '18 at 15:33 — Juan Carlos Oropeza, Jan 24 '18 at 15:33
the sum of Q1,Q2, Q3 in QUADRI – Massimo Griffani Jan 24 '18 at 15:44 — Massimo Griffani, Jan 24 '18 at 15:44

score 2 · Answer 1 · answered Jan 25 '18 at 08:28

You do need to use dynamic sql to do this. Celle knows about all the columns it needs from quadri so you could drive the creation of the dynamic statements from this fact. Using a cursor is as good a way to do it as any.

drop table if exists quadri;
create table quadri(ID varchar(2),SBR_750 int, b10C int, TGI int);
insert into quadri values
('Q1' ,   0 ,     1 ,   0),
('Q2' ,   2 ,     1 ,   0),
('Q3' ,   1 ,     0 ,   1);

drop table if exists celle;
create table CELLE (CELLANAME varchar(20) ,NEEDED int,READY int);
insert into celle values
('SBR_750'  ,  NULL ,  12),
('b10C'     ,  NULL ,  10),
('TGI'      ,  NULL ,   5);

drop procedure if exists `AggiornaCelle`;
delimiter $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `AggiornaCelle`()
begin
DECLARE done INT DEFAULT FALSE;
declare col_name varchar(20);
declare cur1 CURSOR FOR 
 SELECT cellaname FROM celle ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

open cur1;
read_loop: loop
        fetch cur1 into col_name;
        if done then leave read_loop; end if;
        set @sqlstr = concat('update celle set needed = (select sum(',col_name,') from quadri) where cellaname = ', char(39),col_name,char(39),';'); 
        insert into debug_table (msg) values(@sqlstr);
        prepare stmt from @sqlstr;
        execute stmt ;
        deallocate prepare stmt;
end loop;
close cur1;
end $$
delimiter ;

truncate table debug_table;
call `AggiornaCelle`();
select * from debug_table;
select * from celle;

MariaDB [sandbox]> select * from debug_table;
+----+------------------------------------------------------------------------------------------+------+
| id | msg                                                                                      | MSG2 |
+----+------------------------------------------------------------------------------------------+------+
|  1 | update celle set needed = (select sum(SBR_750) from quadri) where cellaname = 'SBR_750'; | NULL |
|  2 | update celle set needed = (select sum(b10C) from quadri) where cellaname = 'b10C';       | NULL |
|  3 | update celle set needed = (select sum(TGI) from quadri) where cellaname = 'TGI';         | NULL |
+----+------------------------------------------------------------------------------------------+------+
3 rows in set (0.00 sec)

MariaDB [sandbox]> select * from celle;
+-----------+--------+-------+
| CELLANAME | NEEDED | READY |
+-----------+--------+-------+
| SBR_750   |      3 |    12 |
| b10C      |      2 |    10 |
| TGI       |      1 |     5 |
+-----------+--------+-------+
3 rows in set (0.00 sec)

The debug_table only exists so that I can check the update statements.

FANTASTIC! Thank you very much – Massimo Griffani Jan 25 '18 at 08:52 — Massimo Griffani, Jan 25 '18 at 08:52

Ravi · Answer 2 · 2018-01-24T16:08:50.957

0

As per documentation

set @s='update celle set needed=(select sum(?) from quadri) where cellaname=?';

You are supposed to pass two argument.

execute stmt using @NC;

should be or something similar

execute stmt using @NC, @NC;

@NC would be same as you are trying to update a row in celle, which is same as column name in quadri table.

edited Jan 24 '18 at 16:08

answered Jan 24 '18 at 15:26

Ravi

30,829
42
119
173

Thanks. No more error but the sum result is wrong: 0 – Massimo Griffani Jan 24 '18 at 15:30
@MassimoGriffani I have updated the post, there were issue in your SQL. Let me know, if your problem solved – Ravi Jan 24 '18 at 15:43
the column from which I want to extract the sum is also variable – Massimo Griffani Jan 24 '18 at 15:48
@MassimoGriffani let us know, if your problem was solved – Ravi Jan 24 '18 at 15:59
Tomorrow I will do – Massimo Griffani Jan 24 '18 at 15:59
In this case is the same name, so `using @NC, @NC;` – Juan Carlos Oropeza Jan 24 '18 at 16:01
@JuanCarlosOropeza I didn't get you. Since, value would be different for both placeholder – Ravi Jan 24 '18 at 16:03
should be the same `SBR_750`, first is a fieldname for the SUM, then is row key for the update – Juan Carlos Oropeza Jan 24 '18 at 16:06
@JuanCarlosOropeza ahhh.. I didn't see the data. Exactly. :D. Updated, thanks for let me know. – Ravi Jan 24 '18 at 16:07
Hey Ravi, I try several way to make it work but couldn't. Please take a look http://sqlfiddle.com/#!9/284837/1 The only way was hardcoding the fieldname on the `SUM` otherwise get 0 as result. – Juan Carlos Oropeza Jan 24 '18 at 16:38
@JuanCarlosOropeza Yes, even I thought so. I'm not in touch with mysql nowadays. But, I think, you are correct. I will update my post – Ravi Jan 24 '18 at 16:41

How to update table using execute statement?

2 Answers2