CODEWITHSUNDEEP
mysqlstored-proceduressql-update
randytan
randytan

Reputation: 1039

MySQL UPDATE Statement Fails From A Sub Query

I have some stupid mistake. I want to update some table using this stored procedure statement.

The logic is: update table x where the rows originally from select statement.

This stored procedure always fails:

DELIMITER $$

USE `sre`$$

DROP PROCEDURE IF EXISTS `sp_generateGraphicsAcc`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_generateGraphicsAcc`(IN sa INT, IN thn VARCHAR(12), IN acc VARCHAR(12))
BEGIN
/*tmp_totalpesan.type_m = 1*/
/*first, update tmp_totalpesan to empty row(s)*/
UPDATE tmp_totalpesan SET totalpesan = '' WHERE type_m = 1;
/*second, update tmp_totalpesan from select statement.*/
UPDATE tmp_totalpesan tt
   SET (tt.totalpesan = 
        SELECT COUNT(mp.quantity)
        FROM mt_pesanan mp
        WHERE (mp.ms_salesarea_idms_salesarea = sa )
        AND (mp.tahun = thn)
        AND (check_aksesoris_barang(mp.ms_langganan_idms_kodebarang) = acc)
        AND tt.bulan = alter_monthname(MONTHNAME(mp.tgl_pesan))
    GROUP BY mp.bulan)
WHERE type_m = 1;       
END$$

DELIMITER ;

the error is shown up

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(tt.totalpesan = SELECT COUNT(mp.quantity) FROM mt_pesanan mp WH' at line 8

Can you see where the problem is? Thanks.

UPDATE

what this stored procedure want to achieve is:

let say i have 1 tables it is transaction table and i want to generate result from specific parameter.

the result from SELECT bla bla bla using this query:

 SELECT COUNT(mp.quantity), alter_monthname(MONTHNAME(tgl_pesan))
 FROM mt_pesanan mp
 WHERE (mp.ms_salesarea_idms_salesarea = sa )
 AND (mp.tahun = thn)
 AND (check_aksesoris_barang(mp.ms_langganan_idms_kodebarang) = acc)
 GROUP BY mp.bulan;

the result is

and so on... (12 rows)

what i want is update the second table with value (1566, 800,etc..) from the select statement in the result.

The same column name is the month from select statement corresponding to the other table.

how to achieve this?

actually the problem

Upvotes: 0

Views: 558

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Your parentheses are not aligned correctly. Do you mean this?

UPDATE tmp_totalpesan tt
   SET tt.totalpesan = 
        (SELECT COUNT(mp.quantity)
         FROM mt_pesanan mp
         WHERE (mp.ms_salesarea_idms_salesarea = sa )
         AND (mp.tahun = thn)
         AND (check_aksesoris_barang(mp.ms_langganan_idms_kodebarang) = acc)
         AND tt.bulan = alter_monthname(MONTHNAME(mp.tgl_pesan))
         GROUP BY mp.bulan
        );

My problem with this is the group by. This subquery could return multiple rows, which will generate an error. If the where clause selects the rows you want to summarize, then you don't need the group by. Also, normally with a column called quantity, I would expect sum() or avg(), but not count(). Are you sure the count() is cwhat you really want?

EDIT:

I think your query is correct without the group by. You are already correlating the month in tt to the month in the subquery:

UPDATE tmp_totalpesan tt
   SET tt.totalpesan = 
        (SELECT COUNT(mp.quantity)
         FROM mt_pesanan mp
         WHERE (mp.ms_salesarea_idms_salesarea = sa )
         AND (mp.tahun = thn)
         AND (check_aksesoris_barang(mp.ms_langganan_idms_kodebarang) = acc)
         AND tt.bulan = alter_monthname(MONTHNAME(mp.tgl_pesan))
        );

However, you might want to add the following in the subquery:

tt.bulan = mp.bulan

Upvotes: 1

Related Questions