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

SQL Server 2012 Forums

Transact-SQL (2012)

Update Query

Author

Topic

beatkeeper25
Starting Member

27 Posts

Posted - 2014-01-22 : 17:28:09

I have a table with CourseIDs and Completion Dates and UserID. Course1 will be replacing Course2. I need to transfer all completion dates from course2 to course1 IF it is more recent. Just having trouble seeing how to approach this. Cursor or set?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-22 : 19:06:22

I'm not convinced that I understand your requirements but...[CODE]declare @Courses table (
CourseID varchar(20),
Completion date,
UserID varchar(20)
)

insert into @Courses (
CourseID,
Completion,
UserID
)
values
('Course1', '20120115', '11111'),
('Course1', '20120115', '22222'),
('Course1', '20120115', '33333'),

('Course2', '20120110', '11111'), -- Same user; Less recent --> No Update
('Course2', '20120120', '22222'), -- Same user; More recent --> Update
('Course2', '20120115', '44444'), -- No User match

('Course3', '20120115', '22222') -- No Course match

select 'Before', *
from @Courses

update c1
set Completion = c2.Completion
from
@Courses c1
inner join
@Courses c2
on c1.CourseID = 'Course1'
and c2.CourseID = 'Course2'
and c1.UserID = c2.UserID
and c1.Completion < c2.Completion

select 'After', *
from @Courses[/CODE]

=================================================
A man is not old until regrets take the place of dreams. - John Barrymore

beatkeeper25
Starting Member

27 Posts

Posted - 2014-01-22 : 20:38:56

A self join huh? Never seen that before. Thats way faster than how I was going about it. Thanks! You guys would probably get a good laugh at my approach. I suck at this!

beatkeeper25
Starting Member

27 Posts

Posted - 2014-01-23 : 12:04:16

OK, that takes care of the case where the user has records for both classes. But I also need to insert a record for the new course if they only have record for old course. Can you do a JOIN like that on an Insert?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 13:15:33

quote:
Originally posted by beatkeeper25

OK, that takes care of the case where the user has records for both classes. But I also need to insert a record for the new course if they only have record for old course. Can you do a JOIN like that on an Insert?

you mean with same completion date as Course1?
then here you go


insert into @Courses (
	CourseID,
	Completion,
	UserID
	)
select 
	'Course2',
	Completion,
	UserID
from @Courses c
WHERE CourseID = 'Course1'
AND NOT EXISTS (SELECT 1
FROM @Courses 
WHERE CourseID = 'Course2'
AND UserID = c.userID
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-23 : 13:19:46

Short answer: Yes!
Longer answer: Yes, but you'd need to do an outer join and test if the Course1 record didn't exist.
What you actually wanted answer:[CODE]insert into @Courses (
CourseID,
Completion,
UserID
)
select
c2.CourseID,
c2.Completion,
c2.UserID
from
@Courses c2
left outer join
@Courses c1
on c1.CourseID = 'Course1'
and c2.CourseID = 'Course2'
and c1.UserID = c2.UserID
and c1.Completion < c2.Completion
where
c1.CourseID is null[/CODE]

=================================================
A man is not old until regrets take the place of dreams. - John Barrymore

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-23 : 13:22:42

In re: "I suck at this!"
Every "expert" on this site started out exactly where you are now. Just keep on trying new things and you'll be the one giving advice sooner than you think.

=================================================
A man is not old until regrets take the place of dreams. - John Barrymore

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources