SHARE
TWEET

Untitled

a guest Feb 10th, 2017 103 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. # you have table1 and table 2 with same schema
  2. select ref, "deleted" as diff from table1
  3.     where ref not in (select ref from table2)
  4. union
  5. select ref, "new" as diff from table2
  6.     where ref not in (select ref from table1)
  7. union
  8. # if more than one column is needed to check if record changed, then you can add it in the `case` statement
  9. # in this example we only have "val" column, but let's say you have "val", "val2" and "val3" columns
  10. # then, to check if record changed the case statement should look like this
  11. # (case when t1.val<>t2.val or t1.val2<>t2.val or t1.val3<>t2.val<>3 then 'changed' else 'same' end)
  12. # then, we would end with a `select` statement looking like this:
  13. # select t1.ref, (case when t1.val<>t2.val or t1.val2<>t2.val or t1.val3<>t2.val<>3 then 'changed' else 'same' end) as diff from table1 t1
  14. select t1.ref, (case when t1.val<>t2.val then 'changed' else 'same' end) as diff from table1 t1
  15.     left join table2 t2 on t2.ref=t1.ref
  16.     where t2.ref=t1.ref;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top