CODEWITHSUNDEEP
sqloracle-databasesql-update
Kofi A
Kofi A

Reputation: 13

How to update a single column with multiple data in oracle 12c

I am updating an oracle table, same column with multiple data. How do i write the code?

I run this code but had error..

I am replacing Boy with Girl, Black with White and Green with Color.

UPDATE BIG
SET
    FNAME = CASE
        WHEN FNAME = 'BOY' THEN 'GIRL'
        WHEN FNAME = 'BLACK' THEN 'WHITE'
        WHEN FNAME = 'GREEN' THEN 'COLOR'
    END;

I need to be able to write a single statement that can update same column with different and multiple data.

Upvotes: 1

Views: 41

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176144

You need to handle ELSE(all rows will be updated some using A -> A (identity):

UPDATE BIG
SET
    FNAME = CASE FNAME
        WHEN 'BOY' THEN 'GIRL'
        WHEN 'BLACK' THEN 'WHITE'
        WHEN 'GREEN' THEN 'COLOR'
        ELSE FNAME
    END;

or better filter rows you want to update:

UPDATE BIG
SET
    FNAME = CASE
        WHEN FNAME = 'BOY' THEN 'GIRL'
        WHEN FNAME = 'BLACK' THEN 'WHITE'
        WHEN FNAME = 'GREEN' THEN 'COLOR'
    END;
WHERE FNAME IN('BOY', 'BLACK', 'GREEN');

<=>

UPDATE BIG
SET FNAME = DECODE(FNAME, 'BOY', 'GIRL', 'BLACK', 'WHITE', 'GREEN', 'COLOR')
WHERE FNAME IN('BOY', 'BLACK', 'GREEN');

Upvotes: 2

Related Questions