US>To upload the results of a MySQL-created stored procedure to an existing table

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

php mysql sql smarty

2022-09-30 11:57

1 Answers

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.

2022-09-30 11:57

If you have any answers or tips

Popular Tags

python x 4566

android x 1593

java x 1484

javascript x 1415

c x 916

c++ x 866

ruby-on-rails x 694

php x 688

python3 x 675

html x 650