CODEWITHSUNDEEP
sqlstringoracle-databasecase
Denis Madroane
Denis Madroane

Reputation: 13

Oracle SQL- Update Query with Case Statement Missing Keyword Error

I'm extremely new to SQL (started learning 3 days ago), so I'm assuming I have some horrendous syntax errors in my code.

I've been at it since yesterday, trying to create an update query that uses cases to modify the value column based on the following conditions:

  1. When value contains the letter 'a', make value uppercase.
  2. When value contains the letter 'd', but doesn't contain the letter 'a', make value lowercase.
  3. When value doesn't meet the conditions above, change the first and last letters from value to 1 and 2.

I have tried to do this a bunch of different ways, but I always end up with some kind of error. This code returns Missing Keyword Error: 

UPDATE t1  
SET value = 
CASE   WHEN value LIKE '%a%' THEN SET value = UPPER(value)   
       WHEN value LIKE '%d%' AND value NOT LIKE '%a' THEN SET value = LOWER(value)   
       ELSE REPLACE(value,'_%','1') AND REPLACE(value,'%_','2') 
END
Where value IS NOT NULL;

Upvotes: 1

Views: 4053

Answers (2)

APC
APC

Reputation: 146349

CASE returns a value so you don't need the SET there. AND is a conditional operator, it's not for running commands together.

REPLACE() does not work the way you're trying to use it. It takes literals not wildcards. So unless value starts and finishes with underscores, '_%' the command won't change anything. Instead of REPLACE() I suggest you use SUBSTR() and concatenate the replacement characters. 

UPDATE t1  
SET value = 
    CASE   WHEN value LIKE '%a%' THEN UPPER(value)   
           WHEN value LIKE '%d%' AND value NOT LIKE '%a' THEN LOWER(value)   
           ELSE '1'||substr(value, 2, length(value)-2) ||'2' 
    END
Where value IS NOT NULL;

Alternatively, you could use the regular expression replace function

ELSE regexp_replace(value, '^(.)(.*)(.)$', '1\22'

However, this has slightly different output when the length of value is 1.

Upvotes: 1

Vecchiasignora
Vecchiasignora

Reputation: 1315

there are many syntex error in Case statment you cant use again SET operation and you cant use separated replace

change

1.SET value = UPPER(value) to UPPER(value) 2.SET value = LOWER(value) to LOWER(value) and 3. remove secondreplace like this

UPDATE t1  
SET value = 
CASE   WHEN value LIKE '%a%' THEN SET UPPER(value)   
       WHEN value LIKE '%d%' AND value NOT LIKE '%a' THEN LOWER(value)   
       ELSE REAPLACE(REPLACE(value,'_%','1'),'%_',2)
END
Where value IS NOT NULL;

Upvotes: 0

Related Questions