将数据从现有行复制到 SQL 中的另一现有行?

问题描述

我有一个表格，其中包含特定课程的跟踪数据，课程编号 6.

I have a table full of tracking data for as specific course, course number 6.

现在我为 11 号课程添加了新的跟踪数据.

Now I have added new tracking data for course number 11.

每一行数据是针对一门课程的一个用户，因此对于分配到课程 6 和课程 11 的用户，有两行数据.

Each row of data is for one user for one course, so for users assigned to both course 6 and course 11 there are two rows of data.

客户希望在 2008 年 8 月 1 日之后的任何时间完成第 6 门课程的所有用户也将完成第 11 门课程的标记.但是，我不能只将 6 转换为 11，因为他们想保留课程的旧数据6.

The client wants all users who have completed course number 6 any time after August 1st 2008 to also have completion marked for course 11. However I can't just convert the 6 to 11 because they want to preserve their old data for course 6.

因此，对于课程编号为 6、标记为已完成且大于 2008 年 8 月 1 日的每一行，我想将完成数据写入包含该特定课程 11 跟踪的行用户.

So for every row that has a course number of 6, is marked as complete, and is greater than the date August 1st 2008, I want to write the completion data over the row that contains the tracking for course 11 for that specific user.

我需要将课程 6 行中的数据转移到课程 11 行，以便将用户分数和发布完成日期等内容移出.

I would need to carry over the data from the course 6 row to the course 11 row so things like user score and date of posted completion is moved over.

这是表的结构:

userID (int)
courseID (int)
course (bit)
bookmark (varchar(100))
course_date (datetime)
posttest (bit)
post_attempts (int)
post_score (float)
post_date (datetime)
complete (bit)
complete_date (datetime)
exempted (bit)
exempted_date (datetime)
exempted_reason (int)
emailSent (bit)

一些值将是 NULL 并且 userID/courseID 显然不会被保留，因为它已经在正确的位置.

Some values will be NULL and userID/courseID obviously won't be carried over as that is already in the right place.

推荐答案

也许我看错了问题，但我相信您已经插入了课程 11 记录，只需更新符合您列出的课程 6 标准的记录数据.

Maybe I read the problem wrong, but I believe you already have inserted the course 11 records and simply need to update those that meet the criteria you listed with course 6's data.

如果是这种情况，您需要使用 UPDATE...FROM 语句:

If this is the case, you'll want to use an UPDATE...FROM statement:

UPDATE MyTable
SET
    complete = 1,
    complete_date = newdata.complete_date,
    post_score = newdata.post_score
FROM
    (
    SELECT
        userID,
        complete_date,
        post_score
    FROM MyTable
    WHERE
        courseID = 6
        AND complete = 1
        AND complete_date > '8/1/2008'
    ) newdata
WHERE
    CourseID = 11
    AND userID = newdata.userID

查看这个相关的 SO 问题了解更多信息

这篇关于将数据从现有行复制到 SQL 中的另一现有行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！