Oracle sql update statement with case

In this scenario, we can use CASE expression. CASE expression is used for selecting or setting a new value from input values. In some cases we need to select and modify the record based on specific conditions.

So instead of using cursor or looping, we can use case CASE expression. Got a solution that runs. Don't know if it is optimal though. There is another workaround you can use to update using a join. This example below assumes you want to de-normalize a table by including a lookup value in this case storing a users name in the table. The update includes a join to find the name and the output is evaluated in a CASE statement that supports the name being found or not found.

The key to making this work is ensuring all the columns coming out of the join have unique names. In the sample code, notice how b. 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, 10 months ago. Active 6 years, 3 months ago. Viewed k times. Improve this question. Hector 6 6 bronze badges. That update should work just fine in Oracle too. Do you get an error? Here's what I have so far which I know does not work:. I know how to do a select based on two tables but that's not very helpful since I don't want to create a new database object - I just want to update an existing object table1 :.

I considered a where clause instead of a case statement but it ends up selecting EVERY record and the 2nd table definitely has different values in the payment field:. I don't have an oracle running right now so I cannot check the syntax propertly , but I think you can try something like this:. 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 8 years ago. Active 8 years ago. Each WHEN clause is executed only once. You can use multiple statements after a WHEN clause, and that the expression in the WHEN clause can be a literal, variable, function call, or any other kind of expression.