Insert based on another column's value (Oracle 11g)

Question

I have a Table as below:

TABLE_1
{
Name varchar2(30)
Value_a number
Update_time timestamp
}

I want to conditionally update the value of update_time during the INSERT + MERGE depending on the value of "value_a". if value_a is less than 0.1, then check if update_time is null. if yes then update else don't. if value_a is greater than 0.1, then check if update_time is not null. if yes then make null.

I have a table_1_stage which I clear, then I insert all the data and then "merge or insert" in table_1 depending on the key match. I am using oracle 11g.

My Prepared Statement looks like the following: " MERGE INTO " + TABLE_1 + " TABLE1 " + " USING TABLE_1_STAGE TABLE1S " + " ON (TABLE1.NAME = TABLE1S.NAME ) " + " WHEN MATCHED THEN " + " UPDATE set VALUE_A = TABLE1S.VALUE_A " + " WHEN NOT MATCHED THEN " + " INSERT ( NAME, VALUE_A) " + " VALUES (?, ?) "

The update_time column is new and i need to set/reset it depending on the value_a.

i know a stored procedure might be a better call but i was looking if something can be done in the insert query to perform this?

Is this an insert or an update and are you using Oracle 11g or Oracle 10g. Can you pleas edit your question to make it consistent with what you're attempting to achieve? — Ben
– Ben, Commented Mar 5, 2014 at 18:16

Jasti · Accepted Answer · 2014-03-05 23:23:57Z

2

Update table1 
set Update_time = (case when value_a < 0.1 and Update_time is null then sysdate
                        when value_a > 0.1 and Update_time is not null then null
                   else Update_time end);

Change sysdate to your desired value.

EDIT:

Include Edit in the merge statement. See the below query (not tested with the real data) In this way we do not run the update on entire table.

Merge into table1 t1
using table1_staging t1s
on t1.name = t1s.name
when matched then
update t1.value_a = t1s.value_a,
t1.Update_time = (case when t1s.value_a < 0.1 and t1.Update_time is null then sysdate
                            when t1s.value_a > 0.1 and t1.Update_time is not null then null
                       else t1.Update_time end)
when not matched then
INSERT (name, values_a)
    VALUES (t1s.name, t1s.values_a);

edited Mar 5, 2014 at 23:23

answered Mar 5, 2014 at 18:20

Jasti

9475 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anuja Khemka Over a year ago

my database might have 10000's of lines. is the "update" statement efficient after the insert has been done? please see my edited post as the prev might have missed some points.

Gordon Linoff · Accepted Answer · 2014-03-05 17:41:15Z

0

Your logic fits very well into an update statement. I think this is what you want:

update table1
    set update_time = sysdate
    where update_time is null and value_a < 0.1 or
          update_time is not null and value_a > 0.1;

You don't say what value you want to set update_time to. I assume it is the current time.

answered Mar 5, 2014 at 17:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Anuja Khemka Over a year ago

Yes, the update_time has to be the "sysdate". my database might have 10000's of lines. is the "update" statement efficient after the insert has been done? please see my edited post as the prev might have missed some points.

rtbf · Accepted Answer · 2014-03-05 19:04:12Z

0

my solution eliminates updating whole table which could have poor performance depending on your data:

update table1 set update_time = (
select case when value_a > 0.1 and update_time is not null then null when value_a < 0.1 and update_time is null then sysdate) where update_time is null and value_a < 0.1 or update_time is not null and value_a > 0.1;

answered Mar 5, 2014 at 19:04

rtbf

1,5972 gold badges17 silver badges36 bronze badges

1 Comment

Anuja Khemka Over a year ago

my database might have 10000's of lines. is the "update" statement efficient after the insert has been done? please see my edited post as the prev might have missed some points.

Collectives™ on Stack Overflow

Insert based on another column's value (Oracle 11g)

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related