CODEWITHSUNDEEP
sqloracle-databasesql-merge
Munu
Munu

Reputation: 135

Update the changed row of target table only using source table if there is any value mismatch between 2 columns

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;

Upvotes: 0

Views: 466

Answers (1)

Maxim Borunov
Maxim Borunov

Reputation: 911

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;

Upvotes: 1

Related Questions