CODEWITHSUNDEEP
sqloracle-database
Lucas Rezende
Lucas Rezende

Reputation: 2686

Change column value when matching condition

I need to replace a NULL value in a column only when other conditions are matched.

Columns: Parent, Child, flag01, lag02

Parent columns has many NULL values, but I want to replace the null values only when flag01 and flag02 is "ok".

If flag01 and flag02 are both "Ok" and Parent is NULL, replace to 'CT_00000'. Else, keep the original value (when NOT NULL).

Upvotes: 9

Views: 52748

Answers (5)

SpiderCode
SpiderCode

Reputation: 10122

Have a look at below SQL Query : 

SELECT 
    CASE 
        WHEN 
            LOWER(flag01) = 'ok' and 
            LOWER(flag02) = 'ok' 
        then 
            ISNULL(Parent, 'CT_00000')
        ELSE 
            Parent 
    END AS 'Parent', 
    flag01, 
    flag02 
FROM 
    [TableName]

Upvotes: 4

M.Ali
M.Ali

Reputation: 69524

UPDATE Table_Name
SET   Column_Name = 'CT_00000'
WHERE flag01 = 'OK'
AND   flag02 = 'OK'
AND   Parent IS NULL

just to select data 

SELECT CASE WHEN (flag01 = 'OK' AND flag02 = 'OK' AND Parent IS NULL)
            THEN 'CT_00000'
            ELSE Column_Name END AS Column_Name
FROM Table_Name

Upvotes: 11

Miller
Miller

Reputation: 1156

I am writing in SQL server, you can have equivalent for others too

Update myTable
set Column_name=ISNUll(Column_name,'CT_00000')
WHERE flag01 = 'OK'
AND   flag02 = 'OK'
AND   Parent IS NULL

or Alternatively 

Update myTable
set Column_name='CT_00000'
WHERE flag01 = 'OK'
AND   flag02 = 'OK'
AND Column_name is null
AND   Parent IS NULL

For select Query

in SQL Server

Select ISNUll(Column_name,'CT_00000')
    from myTable
    WHERE flag01 = 'OK'
    AND   flag02 = 'OK'
    AND   Parent IS NULL

in Oracle

 Select NVL(Column_name,'CT_00000')
            from myTable
            WHERE flag01 = 'OK'
            AND   flag02 = 'OK'
            AND   Parent IS NULL

Upvotes: 1

Jorge Campos
Jorge Campos

Reputation: 23371

So as I though you want a select statement.

select case when (parent is null and flag01 = 'OK' and flag02 = 'OK') 
       then 'CT_00000'
       else parent end as columnSomeName,
       Child, flag01, lag02
 from yourTable

Upvotes: 11

bdn02
bdn02

Reputation: 1500

If you search for an update:

Update tablename
set Parent = 'CT_00000'
Where Parent is null
and flag01 = 'Ok'
and flag02 = 'Ok'

Upvotes: 3

Related Questions