Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

copy

insert

row

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.

like image 965
MetaGuru Avatar asked Mar 19 '09 17:03

MetaGuru


People also ask

How do I move data from one row to another in SQL?

You can move rows from one table to another with the help of INSERT INTO SELECT statement.

How do you replicate rows in SQL?

To select duplicate values, you need to create groups of rows with the same values and then select the groups with counts greater than one. You can achieve that by using GROUP BY and a HAVING clause.


1 Answers

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 > '8/1/2008'     ) newdata WHERE     CourseID = 11     AND userID = newdata.userID 

See this related SO question for more info

like image 51
Michael La Voie Avatar answered Sep 23 '22 21:09

Michael La Voie