Update column based on another column SQL

IamtheWalrus

I have an Oracle query to update 2 column of table. I only want to update the second column if the first a third column is not null.

update tableA
set 
   columnA = 100,
   Case When columnB is not null Then columnC = 300 End
where 
   columnD=60;

My Oracle is complaining that 'Found When, expecting = -or-'

Anyone know why?

seamus

Oracle is not my strong point, but looking at the oracle docs, it should be something more like

update tableA
set 
   columnA = 100,
   columnC = (SELECT Case WHEN columnB is not NULL THEN '300' ELSE ColumnC End)
where 
   columnD=60;

BrokenArrows

You are missing your ELSE in the case.
You need to tell Oracle what to display if your WHEN condition is false.

update tableA
set 
   columnA = 100,
   CASE
       WHEN columnB is not null THEN columnC = 300 
       ELSE 12345
   END
where 
   columnD=60;

IamtheWalrus

I found the answer:

update tableA
set 
   columnA = 100,
   columnC = Case When columnB is not null Then 300 Else null End
where 
   columnD=60;

28064212

That version is setting columnC to null when columnB is not null. What if columnC has a value already, and columnB is not null?

IamtheWalrus

28064212 wrote: »

That version is setting columnC to null when columnB is not null. What if columnC has a value already, and columnB is not null?

Isn't it setting columnC to NULL, unless columnB has a value?

28064212

IamtheWalrus wrote: »

Isn't it setting columnC to NULL, unless columnB has a value?

Your code:

• If colB has a value, set colC to 300
• If colB is NULL, set colC to NULL

If colC is 400 originally, and colB is NULL, should the new value of colC be 400 or NULL?

IamtheWalrus

28064212 wrote: »

Your code:

• If colB has a value, set colC to 300
• If colB is NULL, set colC to NULL

If colC is 400 originally, and colB is NULL, should the new value of colC be 400 or NULL?

I see your point. In my script, in your scenario, columnC's value of 400 will be overridden by NULL.

In my case, columnC will never have a value if columnB is null. ColumnB is a timestamp and columnC is a timezone value.

e.g.

ColumnB | ColumnC
6.00pm | GMT

Do you think the following is better?

update tableA
set 
   columnA = 100,
   columnC = Case When columnB is not null Then 300 Else columnC End
where 
   columnD=60;

Procasinator

I believe the NVL2 function does what you need, so you could do columnB = NVL2(columnB, 300, columnC) for instance.

Boskowski

IamtheWalrus wrote: »

I found the answer:

update tableA
set 
   columnA = 100,
   columnC = Case When columnB is not null Then 300 Else null End
where 
   columnD=60;

Correct, the case/when statement comes after the assignment. In your first example you would assign columnC a value only some of the time. But for every record that qualifies for the update you need to have the same set of updated columns apparently. If you want to retain the original you'd need another when where it goes columnC = columnC.