I do a merge on table and need to update data if they are different. In MSSQL I usually do it by checking a checksum this way:
WHEN MATCHED AND
CHECKSUM(TARGET.Field1,TARGET.Field2, ... TARGET.Field25)
<>
CHECKSUM(TARGET.Field1,TARGET.Field2, ... TARGET.Field25)
THEN UPDATE
SET FIELD1 = FIELD1
How to achieve the same in Oracle?
Oracle's STANDARD_HASH function "computes a hash value for a given expression" (see the documentation). Use the checksums in the WHERE clause of the UPDATE (in the MERGE statement).
Tables for testing (Oracle 12c)
-- 2 tables
create table table1 as
select 1 id, 1 a1, 1 b1, 1 c1, 1 d1, 1 e1, 1 f1 from dual;
create table table2 as
select 2 id, 2 a2, 2 b2, 2 c2, 2 d2, 2 e2, 2 f2 from dual;
SHA256 checksum
-- eg
select
standard_hash ( T.id || T.a1 || T.b1 || T.c1 || T.d1 || T.e1 || T.f1, 'SHA256' )
from table1 T ;
-- output
SHA256
2558A34D4D20964CA1D272AB26CCCE9511D880579593CD4C9E01AB91ED00F325
MERGE
merge into table1 T
using (
select id, a2, b2, c2, d2, e2, f2
from table2
) T2 on ( T.id = T2.id )
when matched then
update
set T.a1 = T2.a2
, T.b1 = T2.b2
, T.c1 = T2.c2
, T.d1 = T2.d2
, T.e1 = T2.e2
, T.f1 = T2.f2
where
standard_hash ( T.id || T.a1 || T.b1 || T.c1 || T.d1 || T.e1 || T.f1, 'SHA256' )
<>
standard_hash ( T2.id || T2.a2 || T2.b2 || T2.c2 || T2.d2 || T2.e2 || T2.f2, 'SHA256' )
when not matched then
insert ( T.id, T.a1, T.b1, T.c1, T.d1, T.e1, T.f1 )
values ( T2.id, T2.a2, T2.b2, T2.c2, T2.d2, T2.e2, T2.f2 )
;
-- 1 row merged
After the MERGE statement has been executed, the tables contain:
SQL> select * from table1;
ID A1 B1 C1 D1 E1 F1
1 1 1 1 1 1 1
2 2 2 2 2 2 2
SQL> select * from table2;
ID A2 B2 C2 D2 E2 F2
2 2 2 2 2 2 2
Modify table2 and MERGE again:
update table2
set a2 = 20, c2 = 30, f2 = 50
where id = 2 ;
insert into table2 ( id, b2, d2, e2 )
values (3, 33, 333, 3333 ) ;
select * from table2;
ID A2 B2 C2 D2 E2 F2
2 20 2 30 2 2 50
3 33 333 3333
Execute the MERGE statement again. Table1 now contains:
SQL> select * from table1;
ID A1 B1 C1 D1 E1 F1
1 1 1 1 1 1 1
2 20 2 30 2 2 50
3 33 333 3333
Try with ORA_HASH, such as this example:
when matched and ora_hash(scol1 || scol2 || scol3 || ... || scol25) <>
ora_hash(...)
then update set
field1 = field2
|| is the concatenation operator.