0

When I run a stored-procedure ranking_long() by mentioning call ranking_long() in a stored-procedure, the result is as follows.

product_id  plg_count   rank
11          6962271     1
10          2705517     2
379         1955067     3
378         196865      4
...........

Now I need to upsert(update when there is the same product_id or insert when there is not the same product_id) the result above into the table called dtb_ranking that has the same structure with the above result (product_id, plg_count, rank) by using a stored-procedure. So I tried the stored-procedure below,

INSERT INTO dtb_ranking (`product_id`,`plg_count`,`rank`) VALUES (CALL `ranking_long`()) ON DUPLICATE KEY UPDATE plg_count = VALUES(plg_count), rank = VALUES(rank);


And I had an error message like below.

One or more errors have occurred while processing your request: Failed to execute a query.：

CREATE DEFINER=xxxxxxx@% PROCEDURE call_ranking_long() 
   NOT DETERMINISTIC
   NO SQL
   SQL SECURITY DEFINER

  INSERT INTO dtb_ranking
    (product_id,plg_count,rank)
  VALUES (CALL ranking_long())
 ON DUPLICATE KEY UPDATE plg_count = VALUES(plg_count), rank = VALUES(rank);

MySQL's message: #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 'CALL ranking_long()) ON DUPLICATE KEY UPDATE plg_count = VALUES(plg_count), ra' at line 1

I would appreciate if anyone could tell me what I should do with the script above(INSERT INTO dtb_ranking...).

user27240
  • 49
  • 10
  • `VALUES (CALL `ranking_long`())` is not possible you need to CALL separated.. – Raymond Nijland Apr 11 '18 at 11:16
  • Thanks, Raymond Nijland. So, I can not state like VALUES (CALL ranking_long()), what exactly should I state in the script for that part. I'm still confused. – user27240 Apr 11 '18 at 11:51
  • There doesn't seem to be a way to do this directly (w/o modifying the stored proc). Take a look at https://stackoverflow.com/questions/687102/mysql-how-to-insert-into-temp-table-from-stored-procedure . – Vatev Apr 11 '18 at 11:59
  • Thanks, Vatev. I'm looking for the similar case for those calls to be separated, but I cann't find. Could you show me an example for anything like this to work? – user27240 Apr 12 '18 at 17:22

0 Answers0