在 SQL 中将数据从一个现有行复制到另一现有行?
我有一个包含特定课程(课程编号 6)的跟踪数据的表。
现在我为课程编号 11 添加了新的跟踪数据。
每行数据针对一门课程的一个用户,因此对于分配给课程 6 和 6 的用户当然11有两行数据。
客户希望所有在 2008 年 8 月 1 日之后完成课程 6 的用户也能完成课程 11。但是我不能只是将 6 转换为 11,因为他们想保留课程 6 的旧数据。
所以对于课程编号为 6、标记为完成且日期大于 2008 年 8 月 1 日的每一行,我想在包含该特定用户的课程 11 的跟踪的行上写入完成数据。
我需要将课程 6 行的数据转移到课程 11 行,以便将用户分数和发布完成日期等内容移过去。
表的结构如下:
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 显然不会被保留,因为它们已经位于正确的位置。
I have a table full of tracking data for as specific course, course number 6.
Now I have added new tracking data for course number 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.
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.
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.
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.
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
也许我读错了问题,但我相信您已经插入了课程 11 记录,只需更新那些符合您在课程 6 数据中列出的条件的记录即可。
如果是这种情况,您需要使用
UPDATE
...FROM
语句:请参阅此相关的 SO 问题以获取更多信息
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.
If this is the case, you'll want to use an
UPDATE
...FROM
statement:See this related SO question for more info
我一直将更新的 From 子句视为正常选择之一。 实际上,如果您想在运行更新之前检查将更新哪些内容,您可以使用 select c11.* 替换更新部分。 请参阅我对跛脚鸭答案的评论。
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.
将一个值从一行复制到同一表(或不同表)中的任何其他符合条件的行:
首先将表别名为 2 个唯一引用,以便 SQL Server 可以区分它们
接下来,指定要复制的字段。
最后,指定控制行选择的条件
根据条件,您可以从单行复制到系列,也可以将系列复制到系列。 您还可以指定不同的表,甚至可以使用子选择或联接来允许使用其他表来控制关系。
Copy a value from one row to any other qualified rows within the same table (or different tables):
Start off by aliasing the table into 2 unique references so the SQL server can tell them apart
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.
使用 SELECT 插入记录
Use SELECT to Insert records
尝试这个:
Try this:
这对于处理整个记录非常有效。
This works well for coping entire records.