带插入和更新的MySQL案例 [英] MySQL Case with Insert and Update

问题描述

在编写条件查询以将数据从一个表复制到另一个表时，我需要帮助，直到我决定要在应用程序中包括版本控制之前，一切都非常简单！

I need help writing a conditional query to copy data from one table to another, everything was pretty straight forward until I decided I wanted to include versioning in my application!

我看了一些示例，但是它们大部分是针对创建mysql过程的，这是我尝试过的:

I have looked at some of the examples, but they are mostly oriented towards creating a mysql procedure, this is what I have tried:

        SELECT CASE 
            WHEN NOT EXISTS (
                SELECT `version`
                FROM `archive_courses`
                WHERE `original_course_id` = '$course_id'
                AND `version` = '$current_version'
            )
            THEN
                BEGIN
                    INSERT INTO `archive_course_users`      <------ syntax error
                    (`course_id`, `user_id`, `course_qty`)
                    SELECT @new_course_id, '$user_id', `course_qty`
                    FROM `current_course_users`
                    WHERE `course_id` = '$course_id'
                    AND `user_id` = '$user_id'
                END
            ELSE
                BEGIN
                    UPDATE `archive_course_users`
                    SET `course_qty` = (SELECT `course_qty` FROM `current_course_users` WHERE `user_id` = '$user_id')
                    WHERE `original_course_id` = '$course_id'
                    AND `user_id` = '$user_id'
                END
            END
        END

似乎情况是/OK/，但在进入我的INSERT查询时会引发语法错误. 我应该怎么做?

It seems the case is working /OK/ but throws a syntax error when it gets to my INSERT query. How should I be doing this?

推荐答案

按照@Rahul的回答，我创建了两个查询:

As per @Rahul's answer I created two queries:

    //INSERT IF NOT EXISTS
        INSERT INTO `archive_course_users`
        (`course_id`, `user_id`, `course_qty`)
        SELECT @new_course_id, '$user_id', `course_qty`
        FROM `current_course_users`
        WHERE NOT EXISTS (
            SELECT `version`
            FROM `archive_courses`
            WHERE `original_course_id` = '$course_id'
            AND `version` = '$current_version'
        )
        AND `course_id` = '$course_id'
        AND `user_id` = '$user_id'

    //UPDATE IF EXISTS
        UPDATE `archive_course_users`
        SET `course_qty` = (SELECT `course_qty` FROM `current_course_users` WHERE `user_id` = '$user_id')
        WHERE EXISTS (
            SELECT `version`
            FROM `archive_courses`
            WHERE `original_course_id` = '$course_id'
            AND `version` = '$current_version'
        )
        AND `original_course_id` = '$course_id'
        AND `user_id` = '$user_id'

这篇关于带插入和更新的MySQL案例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！