Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
beatkeeper25
Starting Member
27 Posts |
Posted - 2013-08-02 : 21:36:42
|
My system has a training module that tracks training courses completed by members.Table1 has the following columns, MemberID (INT), CourseID (INT), CompletionDate (DateTime). There was some error inputting data and I need to fix it. Course1 and Course2 have gotten mixed up. Course1 is outdated and will be removed, but I need to update the CompletionDate for each MemberID from Course1 to Course2. I'm new to TSQL and just trying to explain or visualize what I need done is difficult. Any assistance would be great, let me know if you need any more info.Thanks! |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-08-02 : 22:17:50
|
[CODE]-- TEST DATA:DECLARE @MasterTable TABLE (MemberID INT, CourseID INT, CompletionDate DateTime);INSERT INTO @MasterTable VALUES(1000, 101, '2013-01-16'),(1001, 101, '2013-02-12'),(1002, 101, '2013-03-11'),(1003, 101, '2013-04-25'),(1000, 102, '2012-01-11'),(1001, 102, '2012-02-22'),(1002, 102, '2012-03-30'),(1003, 102, '2012-04-22');-- STEP 1 : CREATE A BACKUP TABLEDECLARE @BackupTable TABLE (MemberID INT, CourseID INT, CompletionDate DateTime);-- STEP 2 : INSERT DATA FROM ORIGINAL TABLE INTO BACKUPTABLEINSERT INTO @BackupTable (MemberID, CourseID, CompletionDate) SELECT MemberID, CourseID, CompletionDate from @MasterTable;-- STEP 3: ENSURE THAT THE DATA IS BACKEDUP CORRECTLYSELECT * FROM @BackupTable;-- STEP 4: UPDATE THE ORIGINAL TABLE; Copy CompletionDate for each MemberID from Course1 to Course2. ;WITH CTE AS(SELECT T1.MemberID, T2.CourseID, T1.CompletionDate from @MasterTable T1 INNER JOIN @MasterTable T2 ON T1.MemberID = T2.MemberID and T1.CourseID = 101 and T2.CourseID = 102)UPDATE TSET CompletionDate = S.CompletionDate FROM @MasterTable T INNER JOIN CTE S ON T.MemberID = S.MemberID and T.CourseID = S.CourseID;-- STEP 5: VERIFY THE UPDATED DATASELECT * FROM @MasterTable;-- STEP 6: DROP THE BACKUP TABLE WHEN YOU ARE CONFIDENT-- OUTPUT BEFORE UPDATE:MemberID CourseID CompletionDate1000 101 2013-01-16 00:00:00.0001001 101 2013-02-12 00:00:00.0001002 101 2013-03-11 00:00:00.0001003 101 2013-04-25 00:00:00.0001000 102 2012-01-11 00:00:00.0001001 102 2012-02-22 00:00:00.0001002 102 2012-03-30 00:00:00.0001003 102 2012-04-22 00:00:00.000-- OUTPUT AFTER UPDATE:MemberID CourseID CompletionDate1000 101 2013-01-16 00:00:00.0001001 101 2013-02-12 00:00:00.0001002 101 2013-03-11 00:00:00.0001003 101 2013-04-25 00:00:00.0001000 102 2013-01-16 00:00:00.0001001 102 2013-02-12 00:00:00.0001002 102 2013-03-11 00:00:00.0001003 102 2013-04-25 00:00:00.000[/CODE] |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-02 : 23:27:57
|
the update would be like this-- TEST DATA:DECLARE @MasterTable TABLE (MemberID INT, CourseID INT, CompletionDate DateTime);INSERT INTO @MasterTable VALUES(1000, 101, '2013-01-16'),(1001, 101, '2013-02-12'),(1002, 101, '2013-03-11'),(1003, 101, '2013-04-25'),(1000, 102, '2012-01-11'),(1001, 102, '2012-02-22'),(1002, 102, '2012-03-30'),(1003, 102, '2012-04-22');UPDATE mSET CompletionDate= CourseDateFROM ( SELECT MAX(CASE WHEN CourseID=101 THEN CompletionDate END) OVER (PARTITION BY MemberID) AS CourseDate, CompletionDate,CourseID FROM @MasterTable )mWHERE CourseID = 102 SELECT * FROM @MasterTableoutput-----------------------------------------------MemberID CourseID CompletionDate-----------------------------------------------1000 101 2013-01-16 00:00:00.0001001 101 2013-02-12 00:00:00.0001002 101 2013-03-11 00:00:00.0001003 101 2013-04-25 00:00:00.0001000 102 2013-01-16 00:00:00.0001001 102 2013-02-12 00:00:00.0001002 102 2013-03-11 00:00:00.0001003 102 2013-04-25 00:00:00.000 and DELETE to remove course1 would beDELETE mFROM @masterTable mWHERE CourseID=101 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
beatkeeper25
Starting Member
27 Posts |
Posted - 2013-08-04 : 20:59:08
|
Thanks guys! Now what if I need to only update the completion dates for Course2 if the current is null, or less than completion date for Course1. Thanks. |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-05 : 02:16:22
|
you mean this?UPDATE mSET CompletionDate= CourseDateFROM ( SELECT MAX(CASE WHEN CourseID=101 THEN CompletionDate END) OVER (PARTITION BY MemberID) AS CourseDate, CompletionDate,CourseID FROM @MasterTable )mWHERE CourseID = 102 AND CompletionDate IS NULLOR CompletionDate < CourseDate SELECT * FROM @MasterTable------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
beatkeeper25
Starting Member
27 Posts |
Posted - 2013-08-05 : 14:03:11
|
I don't understand the purpose of the @mastertable. Can this be done without that? I have copy of prod environment to test on and there is a lot of data in the table in question. Can it be done without making a table variable? |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-05 : 23:54:53
|
quote: Originally posted by beatkeeper25 I don't understand the purpose of the @mastertable. Can this be done without that? I have copy of prod environment to test on and there is a lot of data in the table in question. Can it be done without making a table variable?
the table variable was just for illustration. It just represents your actual table. In actual case just replace it with your actual table name------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|
|
|