SQL code to detect change between two tables without knowing the attributes

Question

I want to try and detect the changes in attributes between two tables. This old answer gets me part of the way but isn't what I really need.

Given the following

If I run

Select 'TABLE1-ONLY' AS SRC, T1.*

 from  (

    Select * from Table_Original as Original

    except

    Select * from Table_Updated as Updated

    ) As T1

Union All

Select 'TABLE2-ONLY' AS SRC, T2.*
 from  (
    Select * from Table_Updated as Updated
    except
    Select * from Table_Original as Original
    ) As T2

I get

One issue in this is that it doesn't only compare the fields when the key is the same (so somewhere there should be 'where Original.Key = Updated.Key'.

But the output I really want is

Ideally this should work in the QGIS SQL implementation but am open to MySQL as well.

All the data above is in https://drive.google.com/drive/folders/1y8EtdCZbNBz9JdvjLGU1N-rN-rK4p8R5?usp=sharing - I added screengrabs as the output looked better this way.

Most people here want sample data and expected result as formatted text, not images. — jarlh, Aug 17 '20 at 07:09
SQL is a _relational_ language, and, as such, you will need to define which column(s) are to be used in finding a match between the two tables. — Tim Biegeleisen, Aug 17 '20 at 07:11
@jarlh - Thanks - the data is in https://drive.google.com/drive/folders/1y8EtdCZbNBz9JdvjLGU1N-rN-rK4p8R5?usp=sharing — GeorgeC, Aug 17 '20 at 07:15

score 1 · Accepted Answer · answered Aug 17 '20 at 08:28

You want a column by column comparison in the results, so you must split the tables in rows where each row is the key and the column's value and then combine them with UNION ALL.
Finally you join the results:

with 
  cte1 as (
    select key, 'name' col, name val from Table_Original union all 
    select key, 'state', state from Table_Original union all 
    select key, 'water', water from Table_Original
  ),
  cte2 as (
    select key, 'name' col, name val from Table_Updated union all 
    select key, 'state', state from Table_Updated union all 
    select key, 'water', water from Table_Updated
  )  
select row_number() over (order by c1.key, c1.col) id,
       c1.key,
       c1.col [Change Field],
       c1.val [Orig Value],
       c2.val [Updated Value] 
from cte1 c1 inner join cte2 c2
on c2.key = c1.key and c2.col = c1.col and c2.val <> c1.val  
order by c1.key, c1.col

See the demo.
Results:

| id  | key | Change Field | Orig Value  | Updated Value |
| --- | --- | ------------ | ----------- | ------------- |
| 1   | 2   | name         | Ray Charles | Ray Tim       |
| 2   | 3   | state        | WA          | SA            |
| 3   | 3   | water        | N           | Y             |
| 4   | 4   | water        | Y           | N             |

that's great. Is there any way to do this without knowing the names of the fields? Can it build the fields from the input tables or would we need to use python and build the query using the structure of your answer? — GeorgeC, Aug 17 '20 at 09:54

SQL code to detect change between two tables without knowing the attributes

1 Answers1