Likely easy UPDATE question - 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

Old Forums

CLOSED - General SQL Server

Likely easy UPDATE question

Author

Topic

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2005-09-19 : 11:46:01

Hi All -- my first post. I've searched many threads trying to find a similar problem but no one is trying to do this update they way I need it.. I think a trigger might be the solution? let me know what you think


UPDATE	W	
	IF W.DESIRED_WANT_DATE < GETDATE()
		SET 	WO.DESIRED_WANT_DATE = DATEADD(day, @amount, W.DESIRED_WANT_DATE)
	ELSE
		SET	WO.DESIRED_WANT_DATE = DATEADD(day, @amount, RWD.REQUIRED_DATE)
	END IF 	
FROM 
	WORK_ORDER W
		JOIN (SELECT *, CASE CHARINDEX(',', USER_1) WHEN 0 THEN USER_1 ELSE LEFT(USER_1, (CHARINDEX(',', USER_1)-1)) END ISSUE_TO FROM WO_WANT_DATE) WO ON (WO.ROWID = W.ROWID)
		JOIN REQUIREMENT_WANT_DATE RWD ON (RWD.BASE_ID = WO.ISSUE_TO AND RWD.PART_ID = WO.PART_ID) 
		JOIN PART P ON (P.ID = W.PART_ID)

Basically I need help with the IFs that follow the UPDATE, is that possible to enclose the SET statements in there?

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-09-19 : 11:56:26

quote:

UPDATE	W	
	IF W.DESIRED_WANT_DATE < GETDATE()
		SET 	WO.DESIRED_WANT_DATE = DATEADD(day, @amount, W.DESIRED_WANT_DATE)
	ELSE
		SET	WO.DESIRED_WANT_DATE = DATEADD(day, @amount, RWD.REQUIRED_DATE)
	END IF

I guess you want to update w.Desired_want_date not wo.Desired_want_date.
If thats true, use case operator:

update w
  set w.Desired_want_date = case 
                              when w.desired_want_date < getDate() then DATEADD(day, @amount, W.DESIRED_WANT_DATE
                              else DATEADD(day, @amount, RWD.REQUIRED_DATE)
                            end
...

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2005-09-19 : 12:41:56

Thanks that looks much more familiar then what I had. I didn't develop the entire query (and I didn't post all of it) but I think WO is a view based on W (WORK_ORDER)

I'll give the case statement a try but I think that'll do it :)

Edit: just following up with a happy thank you and success

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources