Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # you have table1 and table 2 with same schema
- select ref, "deleted" as diff from table1
- where ref not in (select ref from table2)
- union
- select ref, "new" as diff from table2
- where ref not in (select ref from table1)
- union
- # if more than one column is needed to check if record changed, then you can add it in the `case` statement
- # in this example we only have "val" column, but let's say you have "val", "val2" and "val3" columns
- # then, to check if record changed the case statement should look like this
- # (case when t1.val<>t2.val or t1.val2<>t2.val or t1.val3<>t2.val<>3 then 'changed' else 'same' end)
- # then, we would end with a `select` statement looking like this:
- # 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
- select t1.ref, (case when t1.val<>t2.val then 'changed' else 'same' end) as diff from table1 t1
- left join table2 t2 on t2.ref=t1.ref
- where t2.ref=t1.ref;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement