Better way to Update only those values changed?
-
Hi, All: I am working on a system that receives files, loads the data into a table dbo.CountyInput, validates the data in CountyInput, then updates only the changed values in the table dbo.CaseCounty. I am currently using a methodical process of comparing existing data with the new data, then setting a flag to indicate it has changed. There are also some simple rules to determine if the value has really changed. I have about 22 different columns that could change, so that means 22 flags. Not what one would call an elegant solution. Is there a better way to do this? Here is my DDL:
CREATE TABLE dbo.CaseCounty (
SSN SSN NOT NULL ,
CountyCode CountyID NOT NULL ,
LocalCode smallint NOT NULL ,
FipsStateCode char (2) NULL ,
FipsCountyCode CountyID NULL ,
DCSSAddDate smalldatetime NOT NULL ,
DCSSDeleteDate smalldatetime NULL ,
InvestigatorID varchar (5) NULL ,
CaseID varchar (15) NULL ,
CaseType char (1) NULL ,
LastActivityDate smalldatetime NULL ,
EDDActiveCase char (1) NULL ,
CurrentTANFFTBDebt decimal(9, 2) NULL ,
CurrentNonTANFFTBDebt decimal(9, 2) NULL ,
CurrentTANFIRSDebt decimal(9, 2) NULL ,
CurrentNonTANFIRSDebt decimal(9, 2) NULL ,
CurrentOffsetPercent smallint NULL ,
CurrentEddDebt decimal(9, 2) NULL ,
CurrentSupportObligation decimal(9, 2) NULL ,
BirthDate smalldatetime NULL ,
CofName varchar (30) NULL ,
Address varchar (45) NULL ,
City varchar (45) NULL ,
StateCountry varchar (45) NULL ,
ZipCode varchar (9) NULL ,
MailCode char (1) NULL ,
CountyTransDate smalldatetime NULL ,
ResultCode char (3) NULL ,
StatusDate smalldatetime NULL ,
RowIdent int IDENTITY (100, 1) NOT NULL ,
CONSTRAINT PK__CaseCounty__4AB81AF0 PRIMARY KEY CLUSTERED
(
RowIdent
 
)
END
CREATE TABLE dbo.CountyInput (
TransactionType smallint NOT NULL ,
CountyCode CountyID NULL ,
LocalCode char (3) NULL ,
ProcessYear smallint NOT NULL ,
NCPSSN SSN NOT NULL ,
NCPLName varchar (15) NULL ,
NCPFName varchar (10) NULL ,
NCPMidInit char (1) NULL ,
NCPBirthDate smalldatetime NULL ,
CaseID varchar (15) NULL ,
CaseInvestigatorID varchar (5) NULL ,
NCPAddressLine2 varchar (30) NULL ,
NCPAddress varchar (30) NULL ,
NCPCity varchar (20) NULL ,
NCPStateCountry varchar (20) NULL ,
NCPZipCode varchar (9) NULL ,
NCPMailCode smallint NULL ,
FIPSState char (2) NULL ,
FIPSCounty smallint NULL ,
INTCIrsAdmTanfAmt decimal(9, 2) NULL ,
INTCIrsAdmNTanfAmt decimal(9, 2) NULL ,
INTCFtbTanfAmt decimal(9, 2) NULL ,
INTCFtbNTanfAmt decimal(9, 2) NULL ,
INTCEddAmt decimal(9, 2) NULL ,
INTCEddPercent smallint NULL ,
INTCCaseType char (1) NULL ,
AssgnMedicalSum decimal(9, 2) NULL ,
CountyTransferCode smallint NULL ,
CurrentSupport decimal(9, 2) NULL ,
FMSOffSetExclusion varchar (40) NULL ,
CountyTransDate smalldatetime NULL ,
CreationDate datetime NOT NULL CONSTRAINT DF__CountyInp__Creat__5A4F643B DEFAULT (getdate()),
ErrorCode ErrorCd NULL ,
ResultCode char (3) NULL ,
StatusDate smalldatetime NULL ,
RowIdent int IDENTITY (100, 1) NOT NULL ,
CONSTRAINT PK_CountyInput PRIMARY KEY CLUSTERED
(
RowIdent
 
)
END
I SELECT CaseCounty.CaseID into @CurrentCaseID and CountyInput.CaseID into @NewCaseID. Then:
-- if @newCaseID is NULL, don't change it
IF (@NewCaseID IS NOT NULL AND @NewCaseID <> @CurrentCaseID)
SET @ChangeCaseIDFlag = 1
When I have examined all the new values and old values,
UPDATE c
SET CaseID = CASE @ChangeCaseIDFlag WHEN 1 THEN @NewCaseID ELSE c.CaseID END
.
. -- 21 other columns here
.
FROM dbo.CaseCounty AS c
WHERE c.SSN = @ParmSSN
AND c.CountyCode = @ParmCountyCode
Surely there is a better way to do this? Maybe the way of The Force?
Thanks for any ideas.
There is no "i" in team, but idiot has two. -
Why store flag or use CASE expression for each column? if at least one single column changes for a particular row, update all columns you can. By definition, changed columns will be changed, and same columns will remain the same!
-
Master Yoda: I don't understand your point. But here is my second try:
UPDATE dbo.CaseCounty
SET LocalCode = COALESCE (
(SELECT i.LocalCode
FROM dbo.CountyInput AS i
WHERE i.RowIdent = @ParmCountyInputRowIdent), LocalCode)
, SET FipsStateCode = COALESCE (
(SELECT i.FIPSState
FROM dbo.CountyInput AS i
WHERE i.RowIdent = @ParmCountyInputRowIdent), FipsStateCode)
, SET FipsCountyCode = COALESCE (
(SELECT i.FIPSCounty
FROM dbo.CountyInput AS i
WHERE i.RowIdent = @ParmCountyInputRowIdent), FipsCountyCode)
, SET InvestigatorID = COALESCE (
(SELECT i.CaseInvestigatorID
FROM dbo.CountyInput AS i
WHERE i.RowIdent = @ParmCountyInputRowIdent), InvestigatorID)
, SET CaseID = COALESCE (
(SELECT i.CaseID
FROM dbo.CountyInput AS i
WHERE i.RowIdent = @ParmCountyInputRowIdent), CaseID)
, SET CaseType = COALESCE (
(SELECT i.INTCCaseType
FROM dbo.CountyInput AS i
WHERE i.RowIdent = @ParmCountyInputRowIdent), CaseType)
.
.
WHERE SSN = @ParmSSN AND CountyCode = @ParmCountyCode
SSN and CountyCode make a row unique in dbo.CaseCounty; RowIdent is unique in dbo.CountyInput. But doing it this way, there are 22 subqueries. Is there a better way? I need to update dbo.CaseCounty when the corresponding column in dbo.CountyInput IS NOT NULL.
There is no "i" in team, but idiot has two. -
you can do something like :
select @a=col1, @b-2=col2,@c=col3 from yourtable
update YourTable
set col1 = case when @a = @newa then @a
else @newa
end
where col1 = @a and col2=@b and col3 = @C
--where @newa is the value that is passed to the stored proc...you can add more columns and use case for each one..
you are prbly better off just updating all the columns..dpends on your requirement..
hth
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
IMHO, Yoda already gave the solution to your problem. Why make things harder and more difficult than they need to be? I think you should follow the way of the Yedi
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sorry, I still don't understand. "if at least one single column changes for a particular row, update all columns you can. By definition, changed columns will be changed, and same columns will remain the same!" Can you give me an example?
There is no "i" in team, but idiot has two. -
You don't need to compare old to new. If you originally loaded variables with the values from the table, and then change those variables, all you need to do is update the row in the table with ALL the variable values. The ones you changed will update with new values, the ones you didn't change will update with, well, the same old values.
Steve
-
Okay, I might have had a glas of Californian Zinfandel too much right now, but I'll try. Therefore, let's make it easy and assume we have a table with only two columns c1 and c2. Now, the first time you store there the values 1 and 1. You now want to update that column because c2 has changed and should contain now a value of 2. Remember that an UPDATE behind the scenes consists of a DELETE and an INSERT operation. And because of this it is somewhat irrelevant if you update only one column or the whole row. Hm, hope I got it all right together
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Don't make it harder than it has to be. Why read all those columns into variables, setting all those flags, etc.
when you can do it in a single Update statement.UPDATE c
SET CaseID = CASE d.CaseID is not null and d.CaseID <> c.CaseID then d.CaseID else c.CaseID END
.
. -- 21 other columns here
.
FROM dbo.CaseCounty AS c inner join dbo.CountyInput d on c.SSN = d.NCPSSN
AND c.CountyCode = d.CountyCode
WHERE c.SSN = @ParmSSN
AND c.CountyCode = @ParmCountyCode
Basically, join your two tables and update the one.
-
OK, I think I got it now. Thanks, All.
And Frank, what are you doing drinking California Zinfandel? They make it a couple of miles east of here, but didn't you guys invent it?
There is no "i" in team, but idiot has two. -
And Frank, what are you doing drinking California Zinfandel? They make it a couple of miles east of here, but didn't you guys invent it?
AFAIK, is Zinfandel "the" classical American wine. This one was a present by a broker friend of mine. For everyday's purposes it is too expensive here. I guess that bottle was about 30-40 Euros. Far beyond my possibilities. But it was very good... Hm, I think I could like your place
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
There are certain times I want to trigger something when the actual value changes, but not when the value is updated to itself. For example, we have a web application (3rd party) that will update certain records in our database. If a specific column in a row changes (the web app always updates the entire row if even one field is changed) I want to send an export to our mainframe.
Instead of storing the field update event in a bit flag, consider storing it as a row in an "Audit" table. Insert a new row (with the minimum data you need, like the column name and the new data) by writing an Update trigger than joins the deleted and inserted logical tables and comparing the column in question's values to each other (remember to handle NULL if necessary) in the Where clause.
NOTES: Column name should actually be a integer that resolves to the actual column name in a lookup. Keep your Audit table as small as possible, and keep in same database. Do not do any lookups in your trigger. Use set nocount on and set nocount off. Test your trigger for performance degradation.
Signature is NULL
-
Calvin: Thanks for the idea. I actually started with a trigger, doing it pretty much how you describe. But I need to deal with groups of columns that have changed, so it became more straightforward to just do a stored proc that tests all the values then updates only the columns that changed. We are building fixed record length output files for submission to a mainframe.
There is no "i" in team, but idiot has two. -
Whatever works for you. In triggers you also have the option of testing groups by using:
IF UPDATE (column1) OR IF UPDATE (column2)
Triggers can be really tricky, but they are useful if correctly written. One of the nice thing about triggers is that you can control ALL updates to a table, not just those done through a specific proc.
cl
Signature is NULL
-
The problem with IF UPDATE is that it cannot determine if the value actually changed only that the value was explicitly set.
Ex.
TableX
Col1
Col2
Col1 Col2
---- ----
A B
If I do and update with SET Col2 = 'C' then IF UPDATE will pickup change only requested on Col2.
If I do and update with SET Col1 = 'D' then IF UPDATE will pickup change only requested on Col1.
But if I do SET Col1 = 'A', Col2 = 'C' it will say both COl1 and COl2 changed even thou Col1s data did not change.
The best method is to use and audit table when updates occurr, this way you not only can see changes but you can see which are new rows and if should happen which rows may have been removed.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply