Update Query - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Query

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 TABLE
DECLARE @BackupTable TABLE (MemberID INT, CourseID INT, CompletionDate DateTime);

-- STEP 2 : INSERT DATA FROM ORIGINAL TABLE INTO BACKUPTABLE
INSERT INTO @BackupTable (MemberID, CourseID, CompletionDate) SELECT MemberID, CourseID, CompletionDate from @MasterTable;

-- STEP 3: ENSURE THAT THE DATA IS BACKEDUP CORRECTLY
SELECT * 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 T
SET 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 DATA
SELECT * FROM @MasterTable;

-- STEP 6: DROP THE BACKUP TABLE WHEN YOU ARE CONFIDENT

-- OUTPUT BEFORE UPDATE:
MemberID CourseID CompletionDate
1000 101 2013-01-16 00:00:00.000
1001 101 2013-02-12 00:00:00.000
1002 101 2013-03-11 00:00:00.000
1003 101 2013-04-25 00:00:00.000
1000 102 2012-01-11 00:00:00.000
1001 102 2012-02-22 00:00:00.000
1002 102 2012-03-30 00:00:00.000
1003 102 2012-04-22 00:00:00.000

-- OUTPUT AFTER UPDATE:
MemberID CourseID CompletionDate
1000 101 2013-01-16 00:00:00.000
1001 101 2013-02-12 00:00:00.000
1002 101 2013-03-11 00:00:00.000
1003 101 2013-04-25 00:00:00.000
1000 102 2013-01-16 00:00:00.000
1001 102 2013-02-12 00:00:00.000
1002 102 2013-03-11 00:00:00.000
1003 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 m
SET CompletionDate= CourseDate
FROM (
		SELECT MAX(CASE WHEN CourseID=101 THEN CompletionDate END) OVER (PARTITION BY MemberID) AS CourseDate,
		CompletionDate,CourseID
		FROM @MasterTable     
      )m
WHERE CourseID = 102  
    
     
      SELECT * FROM @MasterTable

output
-----------------------------------------------
MemberID	CourseID	CompletionDate
-----------------------------------------------
1000	101	2013-01-16 00:00:00.000
1001	101	2013-02-12 00:00:00.000
1002	101	2013-03-11 00:00:00.000
1003	101	2013-04-25 00:00:00.000
1000	102	2013-01-16 00:00:00.000
1001	102	2013-02-12 00:00:00.000
1002	102	2013-03-11 00:00:00.000
1003	102	2013-04-25 00:00:00.000

and DELETE to remove course1 would be


DELETE m
FROM @masterTable m
WHERE CourseID=101

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 m
SET CompletionDate= CourseDate
FROM (
		SELECT MAX(CASE WHEN CourseID=101 THEN CompletionDate END) OVER (PARTITION BY MemberID) AS CourseDate,
		CompletionDate,CourseID
		FROM @MasterTable     
      )m
WHERE CourseID = 102  
AND   CompletionDate IS NULL
OR CompletionDate < CourseDate

SELECT * FROM @MasterTable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources