Transact sql update using case

What you wrote is fine. Tell me how will you write a sql where you have to update one field only if its null where as other fields should be updated at the same time with no condition.

Are you sure that will always work? Ah, awesome stuff. Unable to do it I want to set the age to Null if it is less than 18 Also I want to set the min. Hi Ben I'm new to your site. Currently i'm working on TeraData. Light Dark. Nice work Ben, I always like to see people leaning on thier database a little to do this kind of work for them, many people would simply break that in to two queries, the first being a SELECT and then use CF conditionals to determine the update type.

Rob, I know what you mean. John, That is awesome! It's amazing what SQL can do once you start digging into it. Regards, Pinal.

Very cool stuff: www. Kenny Oct 23, at AM 2 Comments. Hi, What if I want to "switch" the field name? I have tried this syntax, but it doesn't work. What should I do? Kenny, Are you trying to dynamically select the actual column to update?

Is that what you're trying to do? Kenny Nov 3, at AM 2 Comments. Gonzalo Mar 25, at PM 2 Comments. Ric Feb 4, at AM 1 Comments. Good job. Hi Ben please show me how to update a single row of a particular table by joining with another table in MySql. Goutam, Take a look at this post: www. Manish Jun 26, at AM 4 Comments. Ben, I will defer to Pinal in this case. I know nothing about stored procedures. Alqin Jul 16, at PM 2 Comments. What about if i only want to update on true ignoring the else?

Alqin, I am not sure I understand. Alqin Jul 18, at PM 2 Comments. Now I'm curios about the answer on the real question Ask a question. Quick access. Search related threads. Remove From My Forums. Answered by:. Archived Forums. This is the place for advice and discussions 1 0. Sign in to vote. I have tried this, but without success. I think it's not possible, but I thought I would ask here and see if anyone has done it before. Thanks in advance.

You can't use a condition to change the structure of your query, just the data involved. You could do this:. This is semantically the same, but just bear in mind that both columns will always be updated. This probably won't cause you any problems, but if you have a high transactional volume, then this could cause concurrency issues.

The only way to do specifically what you're asking is to use dynamic SQL. This is, however, something I'd encourage you to stay away from. The solution above will almost certainly be sufficient for what you're after. I want to change or update my ContactNo to where there is using Case statement. I know this is a very old question and the problem is marked as fixed. However, if someone with a case like mine where the table have trigger for data logging on update events, this will cause problem.

Both the columns will get the update and log will make useless entries. The way I did. Now this have another benefit that it does not have unnecessary writes on the table like the above solutions. I believe that you can omit updating the "non-desired" columns by adjusting the other answers as follows:. Now I assume you can write a conditional that handles nulls. For my example, I am assuming you have written such a conditional that evaluates to True, False or Null.

If you need help with this, let me know and I will do my best. Now running these two lines of code does infact change X to 25 if and only if ColConditional is True 1 and Y to 25 if and only if ColConditional is False 0. The null case was never mentioned in the original question or any updates to the question, but as you can see, this very simple answer handles them anyway. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow.

Learn more. Asked 10 years, 11 months ago. Active 1 year ago. Viewed k times. Improve this question. Juan Mellado Assuming all in the same table, yes.