Update ? - 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 2000 Forums

Transact-SQL (2000)

Update ?

Author

Topic

melcraig
Starting Member

39 Posts

Posted - 2006-05-16 : 19:26:48

In t_ParticipantStatus I have 4 fields

EmpID ModuleID StartDate CompleteDate
66 1 10/1/05 10/10/05
77 1 10/6/05 10/8/05
99 1 9/01/05 9/3/05
66 5
99 7

I want to select EmpID, StartDate, CompleteDate where ModuleID = '1' and CompletedDate > '10/1/05'
Then for those EmpID I want to update the StartDate and CompleteDate for those in ModduleID = '5' the same as the StartDate, CompleteDate for ModuleID = '1'
So my results will look like this:

EmpID ModuleID StartDate CompleteDate
66 1 10/1/05 10/10/05
77 1 10/6/05 10/8/05
99 1 9/01/05 9/3/05
66 5 10/1/05 10/10/05
99 7

I think I have completely confused myself!
Thanks for your help!
Mel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-16 : 19:37:22

Here's what I came up with:


SELECT 
	p.EmpId, 
	p.ModuleID, 
	StartDate = 
		CASE 
			WHEN p.ModuleID = 5 AND t.StartDate IS NOT NULL THEN t.StartDate
			ELSE p.StartDate
		END, 
	CompleteDate =
		CASE 
			WHEN p.ModuleID = 5 AND t.CompleteDate IS NOT NULL THEN t.CompleteDate
			ELSE p.CompleteDate
		END
FROM t_ParticipantStatus p
LEFT OUTER JOIN
(
	SELECT EmpID, StartDate, CompleteDate
	FROM t_ParticipantStatus
	WHERE ModuleID = '1' AND CompleteDate > '10/1/05'
) t
ON p.EmpId = t.EmpId

And here's my test:


CREATE TABLE t_ParticipantStatus
(
	EmpID int,
	ModuleID char(1),
	StartDate datetime,
	CompleteDate datetime
)

INSERT INTO t_ParticipantStatus VALUES(66, '1', '10/1/05', '10/10/05')
INSERT INTO t_ParticipantStatus VALUES(77, '1', '10/6/05', '10/8/05')
INSERT INTO t_ParticipantStatus VALUES(99, '1', '9/1/05', '9/3/05')
INSERT INTO t_ParticipantStatus (EmpId, ModuleID) VALUES(66, '5')
INSERT INTO t_ParticipantStatus (EmpId, ModuleID) VALUES(99, '7')

SELECT *
FROM t_ParticipantStatus

SELECT 
	p.EmpId, 
	p.ModuleID, 
	StartDate = 
		CASE 
			WHEN p.ModuleID = 5 AND t.StartDate IS NOT NULL THEN t.StartDate
			ELSE p.StartDate
		END, 
	CompleteDate =
		CASE 
			WHEN p.ModuleID = 5 AND t.CompleteDate IS NOT NULL THEN t.CompleteDate
			ELSE p.CompleteDate
		END
FROM t_ParticipantStatus p
LEFT OUTER JOIN
(
	SELECT EmpID, StartDate, CompleteDate
	FROM t_ParticipantStatus
	WHERE ModuleID = '1' AND CompleteDate > '10/1/05'
) t
ON p.EmpId = t.EmpId

DROP TABLE t_ParticipantStatus

Tara Kizer
aka tduggan

melcraig
Starting Member

39 Posts

Posted - 2006-05-17 : 10:58:02

I think I'm missing something!

If there is a (matching EmpID with ModuleID=1) to EmpID with ModuleID = 5 I want to update the StartDate and CompleteDate for ModuleID=5 from StartDate and CompleteDate for that EmpID with ModuleID = 1.

So I want to change the StartDate and ComleteDate for EmpID 66 for ModuleID 5
EmpID ModuleID StartDate CompleteDate
66 1 10/1/05 10/10/05
66 5

To this:
EmpID ModuleID StartDate CompleteDate
66 1 10/1/05 10/10/05
66 5 10/1/05 10/10/05

I think I'm in an endless confusion loop?
Thanks, Mel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-17 : 11:07:50

That's what my code does. Run my test in pubs or Northwind database to check it out.

Tara Kizer
aka tduggan

melcraig
Starting Member

39 Posts

Posted - 2006-05-17 : 13:08:15

I don't know what I'm doing wrong. All my ModuleID=5 the StartDate and CompleteDate are NULL.

And they are still NULL after I run this.
They do not update with the dates that CompletedDates > 10/1/05 for the ModuleID=1

There are 41 records with for ModuleID=1 with CompletedDate > 10/01/05.
This should update the ModuleID=5 StartDate and CompleteDate

I don't know what's wrong?

I will never get TSql!
Thanks, Mel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-17 : 13:16:26

Did you run my test code in pubs or Northwind to see if it gives you the results that you expect?

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-17 : 13:18:33

When you say you want it to update, do you actually want the data in the database to be updated or just the result set to look like this? My query is a select statement, so it doesn't update any data. It can easily be modified to an update though.

Tara Kizer
aka tduggan

melcraig
Starting Member

39 Posts

Posted - 2006-05-17 : 13:55:53

OMG I am such an idiot. I did run your test on Pubs and it did display the change But when I went back and ran a select with Module = 5 and date > 10/1/05 is was not actually changed.
I actually do want to update the data in the database.
There is NO help for me

Thanks, Mel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-17 : 14:01:32

Here's the corresponding UPDATE statement:


UPDATE p
SET
	StartDate = 
		CASE 
			WHEN p.ModuleID = 5 AND t.StartDate IS NOT NULL THEN t.StartDate
			ELSE p.StartDate
		END, 
	CompleteDate =
		CASE 
			WHEN p.ModuleID = 5 AND t.CompleteDate IS NOT NULL THEN t.CompleteDate
			ELSE p.CompleteDate
		END
FROM t_ParticipantStatus p
LEFT OUTER JOIN
(
	SELECT EmpID, StartDate, CompleteDate
	FROM t_ParticipantStatus
	WHERE ModuleID = '1' AND CompleteDate > '10/1/05'
) t
ON p.EmpId = t.EmpId

Please run this in a test environment first though.

Tara Kizer
aka tduggan

melcraig
Starting Member

39 Posts

Posted - 2006-05-17 : 15:58:29

Thanks Tara for ALL your help.
I will test this in development first!
Mel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources