Nested Insert Statement
-
I'm trying to write a procedure that will insert a new value if one does not exist....or if one does I would like to update it. My "else" and "Where" are tanking.
Any help would be appreciated.
begin
Insert into PhotoXRef(BPCSProdId,Itemid,PhotoName,Location,isactive, campaignId,IMdate)
Values(@BPCSProdId,@ItemId,@PhotoName,@Location,@Isactive,
@campaignId,getdate())
where @itemid not in(select @itemid from photoxref)
end
else
begin
Update PhotoXref Set PhotoName=@PhotoName,Location=@Location,
isactive=@isactive where
itemid=@itemid and BPCSProdId=@BPCSProdId
return @itemId
-
You are not checking for existence.
IF EXISTS( SELECT * FROM PhotoXRef WHERE itemid = @itemid AND BPCSProdId = @BPCSProdId)
BEGIN
INSERT
END
ELSE
BEGIN
UPDATE
END
I wasn't born stupid - I had to study.
-
Your right!
Thanks
-
Except I have it backwards... Oh, dopey me....
Glad this helps...
I wasn't born stupid - I had to study.
-
You can improve performance by performing the UPDATE first and getting rid of the IF EXISTS. The UPDATE takes the place of the EXISTS because it only updates if the key exists.
Update PhotoXref
Set <A href="mailto
hotoName=@PhotoName">PhotoName=@PhotoName, Location=@Location,where itemid=@itemid
-- If no row updated, the key doesn't exist, so insert new record
If @@Rowcount = 0
Begin
Insert into PhotoXRef(BPCSProdId,Itemid,PhotoName,Location,isactive, campaignId,IMdate)
Values(@BPCSProdId,@ItemId,@PhotoName,@Location,@Isactive,@campaignId,getdate())
End
-
Thanks PW. I am set in my ways did not know this... I will use this approach now...
I wasn't born stupid - I had to study.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply