views:

3572

answers:

4

OK so I suck at SQL and I always have to ask for help, not sure what my disconnect is because I don't have any problem with programming C like languages, here is my challenge:

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.

A: 

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 > '08-01-2008'
Chris Doggett
hmm OK, but will I be able to do a WHERE that checks both the INSERT and the SELECT being that this is in the same table?
shogun
Should be able to, give the example I just added a try.
Chris Doggett
Probably should set the complete_date to the current date, too, if that's a requirement. That should get you started, though.
Chris Doggett
+1  A: 

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
    compelte = 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 the accepted solution at the bottom of this link for more info

I hope this helps, good luck!

Michael La Voie
any chance you could explain to me how that newdata thing works, that looks like a handy thing but I'm not quite sure I get it
shogun
this worked, thanks!
shogun
@Ryan did you run it/verified it?
eglasius
The newdata is a table alias. Basically, because the select statement is querying the same table we're updating, SQL would be confused if we said update MyTable.complete_date = MyTable.complete_date. I gave the query for the course=6 data a new name, "newdata", to avoid that confusion.
Michael La Voie
@The lame duck does this actually work as an equivalent of the version I posted? I always see the From clause of an update, as if I were doing an usual select. If it were a select, that would be getting courses filtered by 6 and then it filters by 11 - which gets you an empty result set
eglasius
+1  A: 
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.

eglasius
A: 

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.

Wil Barath