SQL code to update 3 fields

SimonPrice33
SimonPrice33 used Ask the Experts™
on
Hi Experts,

I have been handed someones work and asked to complete it along with make a few enhancements...

What I need help on is the following

The original table that was created has users where an email is sent to, and 9 approval status's

If approver 3, 6 and 7 have an e-mail address that is @myficticiousaddress.com and the rest have @haveityourwar.com is there a way that I can run some SQL from the ASP.Net \ VB.Net Application to say

if, approver3 = approved then  approver6 and 7 also = approved.

if approver 6 = approved then approver 3 and 7 also = approved,

One issue I can foresee with doing this is when an approver is removed or added, it will need to look through all 9 approvers for the e-mail addresses to see if the email address is like @myficticiousaddress.com

I would have liked to have done this differently however this was handed over 95% complete and 80% rubbish..

Any and all help, as always very much appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ramromconsultant

Commented:
Need better picture.
Post the table structure
Show a few rows before-and after.

Author

Commented:
Going to sound like a stupid question, but how do you want me to pass this to you?

screen shot or SQL to create the table structure?
ramromconsultant

Commented:
Either is OK. Or a simple text diagram (using code tags) like:

col1     col2     col3
131      424      344
344      124      345
after
132      424      344
345      124      345

Open in new window

In this case I updated the table setting col1 = col1 + 1
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
I suggest an AFTER UPDATE trigger, to make sure the table stays accurate.

The easy, but inefficient, way would be to use 9 UPDATEs, one per approval, to reset other approval flags.

The much more complex, but possibly more efficient way, would be to handle all 9 in a single UPDATE statement.

Author

Commented:
I would have loved to have done it to set flags if I hadnt been passed this in an almost complete condition...  

Ive got a meeting to run into now, but will send some data across shortly, however I have it working in a away, however its not 100% yet

IF @EmailPos = 'Email1' (there are 9 of these if statements)
BEGIN
	UPDATE T4031_ChangeRequest_ChangeDetail
	SET Email1ApprovalStatus = @Approval
	WHERE ChangeID = @ChangeID and EmailTo1 like '%@myficticiousaddress%'
	UPDATE T4031_ChangeRequest_ChangeDetail
	SET Email2ApprovalStatus = @Approval
	WHERE ChangeID = @ChangeID and EmailTo2 like '%@myficticiousaddress%'
	UPDATE T4031_ChangeRequest_ChangeDetail
	SET Email3ApprovalStatus = @Approval
	WHERE ChangeID = @ChangeID and EmailTo3 like '%@myficticiousaddress%'
	UPDATE T4031_ChangeRequest_ChangeDetail
	SET Email4ApprovalStatus = @Approval
	WHERE ChangeID = @ChangeID and EmailTo4 like '%@myficticiousaddress%'
	UPDATE T4031_ChangeRequest_ChangeDetail
	SET Email5ApprovalStatus = @Approval
	WHERE ChangeID = @ChangeID and EmailTo5 like '%@myficticiousaddress%'
	UPDATE T4031_ChangeRequest_ChangeDetail
	SET Email6ApprovalStatus = @Approval
	WHERE ChangeID = @ChangeID and EmailTo6 like '%@myficticiousaddress%'
	UPDATE T4031_ChangeRequest_ChangeDetail
	SET Email7ApprovalStatus = @Approval
	WHERE ChangeID = @ChangeID and EmailTo7 like '%@myficticiousaddress%'
	UPDATE T4031_ChangeRequest_ChangeDetail
	SET Email8ApprovalStatus = @Approval
	WHERE ChangeID = @ChangeID and EmailTo8 like '%@myficticiousaddress%'
	UPDATE T4031_ChangeRequest_ChangeDetail
	SET Email9ApprovalStatus = @Approval
	WHERE ChangeID = @ChangeID and EmailTo9 like '%@myficticiousaddress%'
END

Open in new window


The problem I have, If I were updating EmailPos1, that has an @haveityourway address it still updates 4, 5 and 6 because they are like @myficticiousaddress

if there a way i can put a condition in for if EmailPos = 'Email1' and not like '%@myficticiousaddress%'

??
i have resolved this now,

in my asp.net code, i have put a condition on the string in to run this stored proc and hit the correct emailapprovalstatus with the corresponding e-mail address.

Author

Commented:
own solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial