Avatar of SimonPrice33
 asked on

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 2008ASP.NETVisual Basic.NET

Avatar of undefined
Last Comment

8/22/2022 - Mon

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

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?

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

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

Open in new window

In this case I updated the table setting col1 = col1 + 1
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Scott Pletcher

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.

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

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


Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

own solution
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.