Copy data from one existing row to another existing row in SQL

Question

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

User · Answer

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  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

User · Answer

This works well for coping entire records   UPDATE your table SET new field   sourse field

User · Answer

Try this   UPDATE barang SET ID FROM SELECT tblkatalog tblkatalog id FROM tblkatalog  WHERE tblkatalog tblkatalog nomor   barang NO CAT  WHERE barang NO CAT  lt  gt

User · Answer

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

User · Answer

Use SELECT to Insert records  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  gt   08-01-2008

User · Answer

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   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  gt   8 1 2008        newdata WHERE     CourseID   11     AND userID   newdata userID   See this related SO question for more info

[sql] Copy data from one existing row to another existing row in SQL?

Examples related to sql

Examples related to insert

Examples related to copy

Examples related to row