Compare the data in a row before the INSERT

Hi,

I would like to check if my data has changed since the last update and if so, another field to 'N/A'.  So I have forename, surname and married status. If a person's married status has changed.  I would like to set married status to 'Yes' otherwise do not update it.

Something like this?....

INSERT INTO TARGET
(FORENAME,
SURNAME,
MARRIED_STATUS)
SELECT
FORENAME,
SURNAME,
CASE WHEN TARGET.SURNAME <> SOURCE.SURNAME THEN 'YES' ELSE TARGET.MARRIED_STATUS
FROM SOURCE
Coco BeansDesignerAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
In this case we need MERGE Statement. Please try like this-

--

MERGE INTO TARGET T
USING Source S ON S.SomeIDColumn = T.SomeIDColumn /*or Some Primary Key column*/
WHEN MATCHED THEN UPDATE 
	SET 
    T.FORENAME = S.FORENAME
   ,T.SURNAME = S.SURNAME
   ,T.MARRIED_STATUS = CASE WHEN t.SURNAME <> s.SURNAME THEN 'YES'  
ELSE S.MARRIED_STATUS END
WHEN NOT MATCHED THEN 
INSERT (T.FORENAME,T.SURNAME,T.MARRIED_STATUS)
VALUES (S.FORENAME,S.SURNAME,S.MARRIED_STATUS);

--

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Please try like this -

You have to join the tables using some ID column... or primary key of the tables that uniquely identifies the row.
FROM SOURCE s INNER JOIN TARGET t ON t.SomeIDColumn = s.SomeIDColumn

INSERT INTO TARGET(FORENAME,SURNAME,MARRIED_STATUS)
SELECT s.FORENAME,s.SURNAME,CASE WHEN t.SURNAME <> s.SURNAME THEN 'YES' ELSE t.MARRIED_STATUS END as [MARRIED_STATUS]
FROM SOURCE s INNER JOIN TARGET t ON t.SomeIDColumn = s.SomeIDColumn

Open in new window

0
 
Coco BeansDesignerAuthor Commented:
Thanks.  I think this will just keep inserting more rows rather than update existing rows?  I would like to insert news if they don't exist in the target but they must update existing rows when they do.  I can't delete the table before the insert or I won't be able to do the compare?
0
 
ste5anSenior DeveloperCommented:
Just a comment:

What kind of database is this? Homework, hobby? What scenario do you want to cover?

FORENAME, SURNAME, MARRIED_STATUS are not attributes common for a relation.

Person ( PersonID, FirstName, LastName, etc)
Marriage ( PersonID, WithPersonID, ValidFrom, ValidTo)

would be the "natural" data model. Especially as "attributes" named status are normally not a attribute of a tuple, but a tuple of a relation.

p.s. attribute = column, tuple=row, relation = table.
1
 
Coco BeansDesignerAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.