将数据从一个现有行复制到 SQL 中的另一个现有行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/663154/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy data from one existing row to another existing row in SQL?
提问by MetaGuru
I have a table full of tracking data for as specific course, course number 6.
我有一个表格，其中包含特定课程（课程编号 6）的跟踪数据。
Now I have added new tracking data for course number 11.
现在我为课程编号 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.
每行数据对应一个用户的一门课程，因此对于分配到课程 6 和课程 11 的用户，有两行数据。
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.
客户希望在 2008 年 8 月 1 日之后的任何时间完成第 6 课的所有用户也为第 11 课标记完成。但是我不能将 6 转换为 11，因为他们想保留课程 6 的旧数据。
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、标记为完成且日期大于 2008 年 8 月 1 日的每一行，我想在包含该特定用户的课程 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.
我需要将数据从课程 6 行转移到课程 11 行，以便将用户分数和发布完成日期等内容移过去。
Here is the structure of the table:
这是表的结构：
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)
Some values will be NULL and userID/courseID obviously won't be carried over as that is already in the right place.
某些值将为 NULL 并且 userID/courseID 显然不会被保留，因为它已经在正确的位置。
回答by Michael La Voie
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.
也许我读错了问题，但我相信您已经插入了课程 11 的记录，只需更新那些符合您在课程 6 的数据中列出的条件的记录。
If this is the case, you'll want to use an UPDATE...FROMstatement:
如果是这种情况，您将需要使用UPDATE...FROM语句：
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
回答by eglasius
UPDATE c11
SET
c11.completed= c6.completed,
c11.complete_date = c6.complete_date,
-- rest of columns to be copied
FROM courses c11 inner join courses c6 on
c11.userID = c6.userID
and c11.courseID = 11 and c6.courseID = 6
-- and any other checks
I have always viewed the From clause of an update, like one of a normal select. Actually if you want to check what will be updated before running the update, you can take replace the update parts with a select c11.*. See my comments on the lame duck's answer.
我一直查看更新的 From 子句，就像普通选择之一。实际上，如果您想在运行更新之前检查将更新的内容，您可以使用 select c11.* 替换更新部分。请参阅我对跛脚鸭回答的评论。
回答by Wil Barath
Copy a value from one row to any other qualified rows within the same table (or different tables):
将值从一行复制到同一表（或不同表）中的任何其他限定行：
UPDATE `your_table` t1, `your_table` t2
SET t1.your_field = t2.your_field
WHERE t1.other_field = some_condition
AND t1.another_field = another_condition
AND t2.source_id = 'explicit_value'
Start off by aliasing the table into 2 unique references so the SQL server can tell them apart
首先将表别名为 2 个唯一引用，以便 SQL 服务器可以区分它们
Next, specify the field(s) to copy.
接下来，指定要复制的字段。
Last, specify the conditions governing the selection of the rows
最后，指定控制行选择的条件
Depending on the conditions you may copy from a single row to a series, or you may copy a series to a series. You may also specify different tables, and you can even use sub-selects or joins to allow using other tables to control the relationships.
根据条件，您可以从单行复制到系列，也可以将系列复制到系列。您还可以指定不同的表，甚至可以使用子选择或连接来允许使用其他表来控制关系。
回答by Chris Doggett
INSERT tracking (userID, courseID, course, bookmark, course_date, posttest, post_attempts, post_score, post_date, complete, complete_date, exempted, exempted_date, exempted_reason, emailSent)
SELECT userID, 11, course, bookmark, course_date, posttest, post_attempts, post_score, post_date, complete, complete_date, exempted, exempted_date, exempted_reason, emailSent
FROM tracking WHERE courseID = 6 AND course_date > '08-01-2008'
回答by prasetyo
Try this:
尝试这个：
UPDATE barang
SET ID FROM(SELECT tblkatalog.tblkatalog_id FROM tblkatalog
WHERE tblkatalog.tblkatalog_nomor = barang.NO_CAT) WHERE barang.NO_CAT <>'';
回答by David
This works well for coping entire records.
这适用于处理整个记录。
UPDATE your_table
SET new_field = sourse_field
