Update: if changed, update, else not

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update: if changed, update, else not

Author

Topic

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-26 : 00:32:57

I have three tables ImportedData, StoredData and ArchivedData which all have the exact same Table Definitions.
The difference between the three tables is that:-
1. StoredData - contains records for current contacts, up-to-date as at last change.
2. ImportedData - contains records for new and current contacts, possibly with changes to current contact fields.
3. ArchivedData - contains records for current and old contacts, transferred here when the record in StoredData has changed.

All these records get stored with a date of update.
I only wish to update StoredData with ImportedData, if there is a change in one of the fields. I do not wish to update a record that has not changed at all, because I want to preserve the original date.

I have created the code to insert a new contact, when it does not already exist.


INSERT INTO StoredData (ContactID, FirstName, LastName, YPromoDate, YPromoType, [Date])
    SELECT ContactID, FirstName, LastName, YPromoDate, YPromoType, [Date]
    FROM ImportedData
    WHERE (NOT EXISTS
	  (SELECT ContactID, FirstName, LastName, YPromoDate, YPromoType, [Date]
		FROM StoredData AS StoredData_1
		WHERE (ContactID = ImportedData.ContactID)))

Now I wish to create code that first checks to see if there are changes, before updating the same fields with updated data. Can anyone help me with that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-26 : 02:27:16

[code]
update s
set FirstName = i.FirstName,
LastName = i.LastName,
YPromoDate = i.YPromoDate,
YPromoType = i.YPromoType,
[Date] = i.[Date]
from StoredData s
inner join ImportedData i on s.ContactID = i.ContactID
where s.[Date] <> i.[Date]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-26 : 02:43:49

no you misunderstand.

I do not want to check the date at all.

The fields that might change are FirstName, LastName, YPromoDate, YPromoType.
so I need to check if any of these have changed, then if any of these have changed, I do update...

the following select gives me the rows that would change


SELECT     s.ContactID, s.FirstName, s.LastName, s.YPromoDate, s.YPromoType, s.Date
                    
FROM         StoredData INNER JOIN
                      ImportedData ON s.ContactID = i.ContactID
WHERE     (s.FirstName <> zzImport.FirstName) OR
          (s.LastName <> i.LastName) OR
          (s.YPromoDate <> i.YPromoDate) OR
          (s.YPromoType <> i.YPromoType) OR

But if I use my WHERE clause, instead of your Where clause, I am going to get what I want. I think. Going to test it now.
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 02:57:52

Are you by chance using SQL Server 2008?
Then have a look at the MERGE command.

E 12°55'05.63"
N 56°04'39.26"

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-26 : 04:30:53

I am using 2008 yes, everything that come with Visual Web Dev 2008.
Thanks for MERGE, I am looking into it now.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 04:32:34

MERGE gives you the ability to both UPDATE and INSERT in same statement.

E 12°55'05.63"
N 56°04'39.26"

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-26 : 15:59:35

I cannot get the thing to work properly. Been at it the whole day.
I can only get MERGE to do all the rows, either updated or inserted.
Each time I try to do the check for "fields are not similar" before the update, then the syntax is incorrect.


MERGE
INTO StoredData USING ImportedData ON (StoredData.ContactID = ImportedData.ContactID)
WHEN MATCHED THEN
UPDATE    
SET StoredData.firstname = ImportedData.firstname,
    StoredData.Lastname = ImportedData.lastname,
    StoredData.YPromoDate = ImportedData.YPromoDate,
    StoredData.YPromoType = ImportedData.YPromoType,
    StoredData.Date = ImportedData.Date
WHEN NOT MATCHED THEN
INSERT (firstname, lastname, YPromoDate , YPromoType , Date)
VALUES (firstname, lastname, YPromoDate , YPromoType , Date);

Could you please help me with the position and syntax for the checking such as the following example?

SELECT     s.ContactID, s.FirstName, s.LastName, s.YPromoDate, s.YPromoType, s.Date
                    
FROM         StoredData INNER JOIN
                      ImportedData ON s.ContactID = i.ContactID
WHERE     (s.FirstName <> zzImport.FirstName) OR
          (s.LastName <> i.LastName) OR
          (s.YPromoDate <> i.YPromoDate) OR
          (s.YPromoType <> i.YPromoType)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 16:24:25

[code]MERGE INTO StoredData
USING ImportedData ON StoredData.ContactID = ImportedData.ContactID
WHEN MATCHED
AND ( StoredData.firstname <> ImportedData.firstname
OR StoredData.Lastname <> ImportedData.lastname,
OR StoredData.YPromoDate <> ImportedData.YPromoDate,
OR StoredData.YPromoType <> ImportedData.YPromoType,
OR StoredData.Date <> ImportedData.Date
)
THEN UPDATE
SET StoredData.firstname = ImportedData.firstname,
StoredData.Lastname = ImportedData.lastname,
StoredData.YPromoDate = ImportedData.YPromoDate,
StoredData.YPromoType = ImportedData.YPromoType,
StoredData.Date = ImportedData.Date
WHEN NOT MATCHED THEN INSERT (firstname, lastname, YPromoDate, YPromoType, Date)
VALUES (firstname, lastname, YPromoDate, YPromoType, Date);[/code]

E 12°55'05.63"
N 56°04'39.26"

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-26 : 18:02:51

Thanks Peso.
I have a question based on this.
Where you say ...
MERGE INTO StoredData
USING ImportedData ON StoredData.ContactID = ImportedData.ContactID
WHEN MATCHED

Is it possible for me to use an inner join on ImportedData ?

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-26 : 18:11:58

I think I got it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-27 : 02:59:59

Yes. Make the USING part as a derived table.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources