insert or update according the field value

Question

insert or update according the field value

sa.ordekci

Ten Centuries

Points: 1177
More actions
July 16, 2013 at 1:59 am

#295549

Hi. I try to write a stored procedure. First, if record exists, it will look at recordtime. If recordtime is null, it updates record time, else it updates updatedtime. I write a code like below:
set @isexist=(select count(*) from GNL_OgretmenDevamsizlikGirisLog
where OgretmenID=@ogretmenID
and DersYiliID=@dersyiliID
and SinifID=@sinifID
and DersID=@dersID
and
Convert(date,GirisTarihi) = Convert(date,getdate())
)
if(@isexist>0)
begin
end
I will write update code in if state. But I could not write. how can I control recordtime and updatedtime, and update one of them.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Don't run the if exists statement because 2 sessions can run at the same time and both will get 0 and try to insert the same record. Also this way you are accessing the table twice. You can run the update statement first and if it didn't find any record to update, then insert a new record. Bellow is a small demonstration:

UPDATE GNL_OgretmenDevamsizlikGirisLog

SET COL1 = 'SomeValue',

COL2 = CASE WHEN COL2 IS NULL THEN GETDATE() ELSE COL2 END, --update the column only if the old value is null

WHERE...

IF @@ROWCOUNT = 0

INSERT INTO GNL_OgretmenDevamsizlikGirisLog (...

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

-- "upsert"

UPDATE GNL_OgretmenDevamsizlikGirisLog SET

recordtime = CASE WHEN recordtime IS NULL THEN @Newtime ELSE recordtime END,

updatedtime = CASE WHEN recordtime IS NULL THEN updatedtime ELSE @Newtime END

WHERE OgretmenID = @ogretmenID

AND DersYiliID = @dersyiliID

AND SinifID = @sinifID

AND DersID = @dersID

AND CAST(GirisTarihi AS DATE) = CAST(getdate() AS DATE)

IF @@ROWCOUNT > 0

BEGIN

INSERT INTO GNL_OgretmenDevamsizlikGirisLog ...

END

-- or MERGE, which is an expensive way to perform single-row upserts

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sa.ordekci Ten Centuries Points: 1177 More actions · Answer 3

Should I use below bode?

set @isexist=(select count(*) from GNL_OgretmenDevamsizlikGirisLog

where OgretmenID=@ogretmenID

and DersYiliID=@dersyiliID

and SinifID=@sinifID

and DersID=@dersID

and

Convert(date,GirisTarihi) = Convert(date,getdate())

)

if(@isexist>0)

begin

end

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

sa.ordekci (7/16/2013)
Should I use below bode?
set @isexist=(select count(*) from GNL_OgretmenDevamsizlikGirisLog
where OgretmenID=@ogretmenID
and DersYiliID=@dersyiliID
and SinifID=@sinifID
and DersID=@dersID
and
Convert(date,GirisTarihi) = Convert(date,getdate())
)
if(@isexist>0)
begin
end

I think Chris have already done your homework... refer his post please

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/