Multi tool use

Updating a column depending on the value set in another column in the same row

Got a table containing a FLAG column (any char) and two others fields ESD and TD (both date).
The FLAG column must be preceded by either the char E if ESD isn't null for that row, or followed by T if TD isn't null for that row.

FLAG

ESD

TD

FLAG

E

ESD

T

TD

Note

FLAG

ESD

TD

ESD

TD

Example

Before

+-------+-------------+-------------+
| FLAG | ESD | TD |
+-------+-------------+-------------+
| V | 2018/05/01 | (null) |
| D | (null) | (null) |
| V | (null) | (null) |
| V | (null) | 2018/06/31 |
| V | (null) | (null) |
| D | 2018/01/01 | 2018/08/31 |
+-------+-------------+-------------+


After

+-------+-------------+-------------+
| FLAG | ESD | TD |
+-------+-------------+-------------+
| EV | 2018/05/01 | (null) |
| D | (null) | (null) |
| V | (null) | (null) |
| VT | (null) | 2018/06/31 |
| V | (null) | (null) |
| EDT | 2018/01/01 | 2018/08/31 |
+-------+-------------+-------------+


Question

What is the easiest way to get this result in SQL or PL/SQL ?

3 Answers
3

You can use concatenation and two case expressions:

case when esd is not null then 'E' end
|| flag
|| case when td is not null then 'T' end as flag


Demo with your data in a CTE (correcting as June doesn't have 31 days):

with your_table (FLAG, ESD, TD) as (
select 'V', date '2018-05-01', null from dual
union all select 'D', null, null from dual
union all select 'V', null, null from dual
union all select 'V', null, date '2018-06-30' from dual
union all select 'V', null, null from dual
union all select 'D', date '2018-01-01', date '2018-08-31' from dual
)
select
case when esd is not null then 'E' end
|| flag
|| case when td is not null then 'T' end as flag,
esd,
td
from your_table;

FLAG ESD TD
---- ---------- ----------------------------
EV 2018-05-01
D
V
VT 2018-06-30
V
EDT 2018-01-01 2018-08-31


If you really want to update the value in the table then you can use the same thing in an update statement:

update your_table
set flag =
case when esd is not null then 'E' end
|| flag
|| case when td is not null then 'T' end;


but then the flag will be wrong as soon as a null date column is populated, and will be harder to recalculate when you need to. You should either just adjust this as you query it, optionally via a view; or add a virtual column that holds the generated value separately.

Seems like a simple update using CASE statements?

CASE

UPDATE [table] SET FLAG = CASE WHEN ESD IS NOT NULL THEN 'E' ELSE '' END || FLAG || CASE WHEN TD IS NOT NULL THEN 'T' ELSE '' END;


you are updating a data here and you have to do them one by one as follows. If your values are duplicate, like as I see in your table 'D', 'V' and 'VT' once you execute the command displayed below it will update all values defined in the where clause. you wanted to update the ESD and TD column when notnull.

update (tablename)
set Flag = 'ED'
where ESD = 'notnull'

update (tablename)
set Flag = 'TVT'
where TD = 'notnull'

update (tablename)
set Flag = 'ExT'
where TD = 'Notnull' and ESD = 'notnull'

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.