UPDATE with CASE and IN - Oracle

sql oracle sql-update
user641605 picture user641605 · Mar 2, 2011 · Viewed 193k times · Source

I wrote a query that works like a charm in SQL Server. Unfortunately it needs to be run on an Oracle db. I have been searching the web inside out for a solution on how to convert it, without any success :/

The query looks like this i SQL:

UPDATE tab1   SET budgpost_gr1=     
CASE  WHEN (budgpost in (1001,1012,50055))  THEN 'BP_GR_A'   
      WHEN (budgpost in (5,10,98,0))  THEN 'BP_GR_B'  
      WHEN (budgpost in (11,876,7976,67465))     
      ELSE 'Missing' END`

My problem is also that the columns budgetpost_gr1 and budgetpost is alphanumeric and Oracle seems to want to see the list as numbers. The list are variables/parameters that is pre-defined as comma separated lists, which is just dumped into the query.

Answer

Thomas picture Thomas · Mar 2, 2011

You said that budgetpost is alphanumeric. That means it is looking for comparisons against strings. You should try enclosing your parameters in single quotes (and you are missing the final THEN in the Case expression).

UPDATE tab1   
SET budgpost_gr1=   CASE  
                        WHEN (budgpost in ('1001','1012','50055'))  THEN 'BP_GR_A'   
                        WHEN (budgpost in ('5','10','98','0'))  THEN 'BP_GR_B'  
                        WHEN (budgpost in ('11','876','7976','67465')) THEN 'What?'
                        ELSE 'Missing' 
                        END

Related questions

Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More
Update rows in one table with data from another table based on one column in each being equal

Update many rows into one table from another table based on one column in each being equal (user_id). both tables have a user_id column. Need to insert data from t2 into t1 when the user_id column are …

Read More
Update columns with Null values

I tried updating a table as follows: update userloginstats set logouttime = sysdate where logouttime = null; It didn't update the columns with null values. What went wrong?

Read More