0

Table T1

ID  C1  C2  C3
--  --  --  --
1   x   y   z
2   p   q   r
3   a   b   c

Table T2

1   x   y   z
2   p   q1  r
3   a   b   c1

Need to update all values of T2 into T1 to ensure both table should have same value.This can be achieved by merge statement.But in table T1 has a column update_timestamp which will update only if there is any mismatch between 2 table.In merge the update_timestamp updates if there is no mismatch also.In above example the update_timestamp column update for ID no 2 & 3 only. 

MERGE INTO T1
USING T2
ON (T1.ID = T2.ID)
WHEN MATCHED THEN
UPDATE SET T1.C1 = T2.C1,T1.C2 = T2.C2,T1.C3 = T2.C3,T1.update_timestamp=SYSDATE;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Munu
  • 103
  • 5
  • 15

1 Answers1

1

Try this one (in case you track the changes for all 3 columns - c1, c2 and c3):

MERGE INTO T1
USING (
  select id, c1, c2, c3 from T2
  minus
  select id, c1, c2, c3 from T1
) T2
ON (T1.ID = T2.ID)
WHEN MATCHED THEN
UPDATE SET T1.C1 = T2.C1,T1.C2 = T2.C2,T1.C3 = T2.C3,T1.update_timestamp=SYSDATE;
Maxim Borunov
  • 901
  • 5
  • 9