CODEWITHSUNDEEP
sqloracle-database
fizz
fizz

Reputation: 69

How to update data only when the data is changed at oracle?

May I know how to update data only when the data is changed at oracle?

For example, table tb_user

userid, name, email
1234, Peter, [email protected]

I would like to update the data only when one of the field changed.

I tried merged into method. But it would either update or insert without any checking.

Now, i created a tmp table tb_tmp_user to inserted all new coming data to tmp table first. Then, i compared the data to decide insert or update not.

But the methods seem not smart at all.

1st method.

select userid, name, email from tb_tmp_user B A
where userid||name||email not in(
select userid||name||email from tb_user B)

So i can update data which is changed but this method will be too slow when there are lots of data.

2nd method.

select userid, name, email from tb_tmp_user A
where not exists (
select 'x' from tb_user B where  a.userid=b.userid , a.name=b.name, a.email=b.email)

This one is similar to the 1st method. But I do think that there are some better methods to solve above case.

Could anyone provide better ideas for this case?

Upvotes: 0

Views: 1884

Answers (1)

George Joseph
George Joseph

Reputation: 5922

The merge can help achieve your task.

If requirement is to "tag" records for which an update has occurred as per the condition as mentioned and you have a field say "updated_on" which must record only these "changes",then the following can assist.

MERGE
 INTO tb_user a
USING tb_tmp_user b
   ON a.user_id=b.user_id
WHEN MATCHED THEN
UPDATE
   SET a.name=b.name
      ,a.email=b.email
      ,a.updated_on = case when a.name <> b.name 
                             OR (a.name is null and b.name is not null)
                             OR (a.name is not null and b.name is null)
                           then sysdate
                          when a.email <> b.email 
                             OR (a.email is null and b.email is not null)
                             OR (a.email is not null and b.email is null)
                           then sysdate
                          else a.updated_on
                    end;

Upvotes: 1

Related Questions