SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Likely easy UPDATE question
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kbalz
Yak Posting Veteran

USA
94 Posts

Posted - 09/19/2005 :  11:46:01  Show Profile
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

Czech Republic
518 Posts

Posted - 09/19/2005 :  11:56:26  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message
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
...

Edited by - mmarovic on 09/19/2005 11:56:55
Go to Top of Page

Kbalz
Yak Posting Veteran

USA
94 Posts

Posted - 09/19/2005 :  12:41:56  Show Profile
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

Edited by - Kbalz on 09/20/2005 10:00:40
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000