Hello,

I'd like to compare two tables, e.g. old_person vs new_person.
both have the same structure and matching Primary keys.

I would like to return the name of the changed field.

e.g.
old_person:
- ID = 1
- First_Name = 'Lieschen'
- Last_Name = 'Mueller'

new_person:
- ID = 1
- First_Name = 'Lieschen'
- Last_Name = 'Meier'

I would like to do something like:
IF old_person.Last_Name != new_person.Last_name THEN
SET changed_field = "Last name changed"
END IF

And see a list of all persons from new_person with detail, what has
changed (ID, First_Name, Last_Name, changed_field)

what is the best way to do it?
I tried it with a stored procedure, but I can't compare stuff within
my 'select' statement

Kind regards
Ralph

szeta wrote:
> Hello,
>
> I'd like to compare two tables, e.g. old_person vs new_person.
> both have the same structure and matching Primary keys.
>
> I would like to return the name of the changed field.
>
> e.g.
> old_person:
> - ID = 1
> - First_Name = 'Lieschen'
> - Last_Name = 'Mueller'
>
> new_person:
> - ID = 1
> - First_Name = 'Lieschen'
> - Last_Name = 'Meier'
>
> I would like to do something like:
> IF old_person.Last_Name != new_person.Last_name THEN
> SET changed_field = "Last name changed"
> END IF
>
> And see a list of all persons from new_person with detail, what has
> changed (ID, First_Name, Last_Name, changed_field)
>
> what is the best way to do it?
> I tried it with a stored procedure, but I can't compare stuff within
> my 'select' statement
>
> Kind regards
> Ralph
>
but you can compare stuff within the stored procedure. you'd want to
write a stored procedure that takes in an ID. based on the ID, it makes
a comparison between the first name and/or last name of the person and
if any of them is changed it return either or both.

so roughly you'd have something like this:

BEGIN
DECLARE FirstNamDiff Varchar(40);
DECLARE LastNameDiff Varchar(40);
DECLARE FirstNamDiff1 Varchar(40);
DECLARE LastNameDiff1 Varchar(40);

Select First_name, Last_name into FirstNameDiff, LastNameDiff from
old_person where ID = 1;

Select First_name, Last_name into FirstNameDiff1, LastNameDiff1 from
new_person where ID = 1;

IF FirstNameDiff <> FirstNameDiff1 THEN
SET changed_field = "First name changed";
END IF;

IF LastNameDiff <> LastNameDiff THEN
SET changed_field = "Last name changed";
END IF;
END


hope this helps.
--
lark -- EMAIL REMOVED
To reply to me directly, delete "despam".