February 2, 2016 at 4:10 pm
Hi All,
I have two tables,
1. Lookuptable
2.ErrorTable
Lookup table and ErrorTable has a column named (validkey) . It is a varchar.
I am updating the error table with correct data from lookup table and that update is working fine.
But i need to write one condition before the update and the condition is
if (the validkey in error table is present in the lookup table)
then
update the record
else
exit
Please help me how to write that condition .
Thanks in advance !!!
February 2, 2016 at 4:27 pm
if (the validkey in error table is present in the lookup table)
then
update the record
else
exit
UPDATE Table1
SET Field = <something>
WHERE Table1.Field1 = Table2.JoinField
February 2, 2016 at 4:33 pm
This is a CLASSIC data processing problem known as an UPSERT. There are a BUNCH of issues with it related to concurrency. Almost every client I have seen this type of code at has messed it up - because it is very difficult to get right and still allow for a lot of concurrent activity without getting bad data.
If you do a web search for the words sql server upsert concurrency you will find several relevant articles, including bugs with the supposed fix MERGE.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 2, 2016 at 5:00 pm
Hi pietlinden, SQLGuru
Thanks for your advise,
below is the query which i use for updating the table
declare cGetSicErrorData cursor for
select distinct PolicyNine, ErrorSicFour, ErrorSicFifth
from #tmpSic02
open cGetSicErrorData
fetch cGetSicErrorData into @PolicyNine,@SicFour,@SicFifth
--Start
while @@FETCH_STATUS = 0
begin
Select @CorrectSicFifth = NULL
select top 1 @CorrectSicFifth=SicCodeFifth
from SicCodeLib
where SicCodeFour = @SicFour
order by SicCodeFifth
/*
select top 1 @CorrectSicFifth=substring(FifthSic,5,1)
from FifthSicCode
where SUBSTRING(FifthSic,1,4) = @SicFour
*/
select * from PESCodingDataOut
if(@CorrectSicFifth is not NULL)
begin
update PESCodingDataOut
set CodingData = SUBSTRING(CodingData,1,61)+@CorrectSicFifth+SUBSTRING(CodingData,63,26)
where SUBSTRING(CodingData,11,9) = @PolicyNine and
SUBSTRING(CodingData,26,4) = @SicFour and
SUBSTRING(CodingData,1,1) = 'A'
end
fetch cGetSicErrorData into @PolicyNine,@SicFour,@SicFifth
end
Here you can see at the start im fetching
@PolicyNine,@SicFour,@SicFifth from the cursor,
Right next to that statement i want the condition to be checked, only if it is true all the highlighted
statements should execute below, if it fails it should directly go to next element in the cursor.
This is what im trying to achieve, please tell me how can i achieve this
Thanks in advance !!!
February 3, 2016 at 4:28 am
You can read the top posts when you search for that string of words I gave you. You are doing one of the two usual ways of doing this, and you have absolutely nothing in place to prevent the bad data that will come eventually from concurrency problems (unless nothing else EVER touches the same data at the same time anyway). Remember - when you ask for data with a SELECT a shared lock is taken but then RELEASED (unless you do something about it) - and in the short interval between that statement and the conditional and data modification something ELSE can mess with the data too. This is the FUNDAMENTAL problem with UPSERTs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply