mysql - 如何在一个mysql查询中同时使用INSERT INTO和UPDATE？

Question

我必须使用 INSERT INTO 命令从数据库中获取数据，然后在同一个表上我想使用更新命令(这取决于通过 Insert into 命令插入的值)。我想使用一个查询来执行插入和更新命令？

你可以引用我的命令

INSERT INTO `cr_score_table`(`User_Id`,`CR_Id`, `Question_attempted`, `Score`) 
SELECT wp_wp_pro_quiz_statistic_ref.user_id,wp_wp_pro_quiz_statistic.statistic_ref_id, SUM(wp_wp_pro_quiz_statistic.solved_count), SUM(wp_wp_pro_quiz_statistic.correct_count)
FROM wordpress.wp_wp_pro_quiz_statistic_ref
LEFT JOIN wordpress.wp_wp_pro_quiz_statistic
ON wp_wp_pro_quiz_statistic_ref.statistic_ref_id = wp_wp_pro_quiz_statistic.statistic_ref_id
Group By statistic_ref_id
ON DUPLICATE KEY UPDATE cr_score_table.CR_Id=cr_score_table.CR_Id

并且

UPDATE cr_score_table AS X JOIN (
SELECT
  s1.CR_Id, COUNT(s2.CR_Id)/ (SELECT COUNT(*) FROM cr_score_table) * 100 AS percentile
FROM
  cr_score_table s1 JOIN cr_score_table s2 on (s2.score <= s1.score)
GROUP BY s1.Cr_Id
ORDER BY s1.Cr_Id) AS Z 
ON (X.Cr_Id = Z.Cr_Id) 
SET X.Score_Percentile = Z.percentile, `Name`= (Select Name
                                     From user_table
                                     WHERE user_table.User_Id = X.User_Id)

Answer 1

您可以使用2个单独的查询并使用mysql

LAST_INSERT_ID()

关于您的 UPDATE 查询