We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Update for a certain condition

Medium Priority
190 Views
Last Modified: 2012-05-07
I want to perform conditional update as follows:

BEGIN
      UPDATE tbMeters SET MAC = @MAC,  
        Model = @Model,  
        LastDataUpdateTime = @ReportTime
        /*---------------Conditional update to be done only when @model ="XC09"-----------*/
        ,R1 = @R1,
        R2 = @R2,
        DC = @DC,
        TT = @TT,
        IT = @IT
          /* --------------- end of -Conditional update----------- */
        WHERE Disable = '0'
END

Thanks,
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Hope this helps:

IF @model ='XC09' AND R1 = @R1 AND R2 = @R2 AND DC = @DC AND TT = @TT AND IT = @IT
UPDATE tbMeters SET MAC = @MAC,  
        Model = @Model,  
        LastDataUpdateTime = @ReportTime
WHERE Disable = '0'
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Or a more simple UPDATE statement like this would do:
UPDATE tbMeters SET MAC = @MAC,  
        Model = 'XC09',  
        LastDataUpdateTime = @ReportTime
WHERE Disable = '0'
 AND R1 = @R1 
 AND R2 = @R2 
 AND DC = @DC 
 AND TT = @TT 
 AND IT = @IT

Open in new window

Author

Commented:
appari:

You recommendation is pretty close but is there any way we can avoid repetition?


Thanks,

Author

Commented:
Do you think this code will work:

If @Model = 'XC09'
    BEGIN
      UPDATE tbMeters SET MAC = @MAC,  
              Model = @Model,  
              LastDataUpdateTime = @ReportTime,
              R1 = @R1,
              R2 = @R2,
              DC = @DC,
              TT = @TT,
              IT = @IT
      WHERE Disable = '0'
      END
else
   BEGIN
     UPDATE tbMeters SET MAC = @MAC,  
              Model = @Model,  
              LastDataUpdateTime = @ReportTime
      WHERE Disable = '0'
END
          
Thanks
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
What do you think If I remove one (BEGIN -END) block?

BEGIN
If @Model = 'XC09'
      UPDATE tbMeters SET MAC = @MAC,  
              Model = @Model,  
              LastDataUpdateTime = @ReportTime,
              R1 = @R1,
              R2 = @R2,
              DC = @DC,
              TT = @TT,
              IT = @IT
      WHERE Disable = '0'
     
else
   
        UPDATE tbMeters SET MAC = @MAC,  
              Model = @Model,  
              LastDataUpdateTime = @ReportTime
        WHERE Disable = '0'
END

Your comment is highly appreciated.

Thanks

Author

Commented:
Very helpful posts
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Not a problem and you can remove it.

BEGIN.. END is required in IF ..ELSE condition, if you are performing more than one operation. Say if you perform two updates if it passes IF condition, then you need to enclose two update statements within BEGIN .. END.

In your case you have a single statement and you may or may not have a BEGIN.. END block itself.

Hope this clarifies
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.