asked on 5/22/2012

SQL code to update 3 fields

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.

Microsoft SQL Server 2008 ASP.NET Visual Basic.NET

Last Comment

SimonPrice33

5/28/2012

ramrom

5/22/2012

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

SimonPrice33

5/22/2012

ASKER

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?

ramrom

5/22/2012

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

Scott Pletcher

5/22/2012

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.

SimonPrice33

5/23/2012

ASKER

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%'

??

ASKER CERTIFIED SOLUTION

SimonPrice33

5/23/2012

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

SimonPrice33

5/28/2012

ASKER

own solution