CODEWITHSUNDEEP
mysqlcompare
Arun Theja Muthyalu
Arun Theja Muthyalu

Reputation: 3

Is there a way in MySQL to compare two rows of same column structure and find out which column values have changed?

I have a problem where I need to compare two different rows of similar column structure and be able to find out which column values have changed?

Table A

ID FIRST_NAME LAST_NAME
1 John Carter
2 Jeff Derney

Table B

ID FIRST_NAME LAST_NAME
1 Johnny Carter
2 Jeff Dunham

Desired Output

ID PRIMARY_KEY OLD_VALUE NEW_VALUE
1 1 John Johnny
2 2 Derney Dunham

Upvotes: 0

Views: 409

Answers (1)

Akina
Akina

Reputation: 42844

SELECT id AS primary_key, 
       tableA.first_name AS old_value, 
       tableB.first_name AS new_value,
       'first name' AS altered_column
FROM tableA
JOIN tableB ON tableA.id = tableB.id 
           AND tableA.first_name <> tableB.first_name 

UNION ALL

SELECT id, 
       tableA.last_name, 
       tableB.last_name,
       'last name'
FROM tableA
JOIN tableB ON tableA.id = tableB.id 
           AND tableA.last_name <> tableB.last_name 

ORDER BY primary_key;

Upvotes: 0

Related Questions