The already created stored procedure, ranking_long, is a newly created stored procedure that only calls it, and by describing it as CALL ranking_long() within its definition:
product_id plg_countrank
11 6962271 1
10 2705517 2
379 1955067 3
378 196865 4
...........
In order to create a stored procedure to upload (update if you have the same product_id, insert if you have the same product_id) to a table dtb_ranking with exactly the same structure, I am trying the following statements, but an error appears (for example, "There are multiple errors in the description."
INSERT INTO dtb_ranking(`product_id`,`plg_count`,`rank`)VALUES(CALL`ranking_long`())ON DUPLICATE KEY UPDATE plg_count=NEW.plg_count,rank=NEW.rank;
Could you tell me what kind of description I need to make to achieve the above objectives?
dtb_ranking Structure
product_id plg_countrank
Description from your instruction:
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);
One or more errors have occurred while processing your request:
Failed to execute query: EACREATE DEFINER=`xxxxxx`@`%`PROCEDURE`call_ranking_long`() NOT DETERMINISTIC NO SQL SECURITY DEFINER INSERT INTO dtb_ranking(`product_id`,`plg_count`rank`) VALUES(CALL`rank_LANKING_ONUPLICAKETE)
MySQL message: #1064 - You have an error in your SQL syntax; check the manual that responses to your MySQL server version for the right syntax to use 'CALL' ranking_long`())ON DUPLICATE KEY UPDATE plg_count = VALUES(plg_count), line 1
Assuming it is properly indexed, I don't know the first half, but I think it would be better to do the following after ON DUPLICATE KEY.
ON DUPLICATE KEY UPDATE plg_count = VALUES(plg_count), rank = VALUES(rank);
https://dev.mysql.com/doc/refman/5.6/ja/insert-on-duplicate.html has
You can use the VALUES(col_name) function in the UPDATE clause to view column values from the INSERT portion of the INSERT...ON DUPLICATE KEY UPDATE statement.
As it says, I think you will now include the value you tried to insert in insert.
© 2024 OneMinuteCode. All rights reserved.