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.
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.
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?
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
after
132 424 344
345 124 345
In this case I updated the table setting col1 = col1 + 1
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.
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.
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
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%'
??
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
own solution
Post the table structure
Show a few rows before-and after.