• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 668
  • Last Modified:

mssql update if already exists

hi
i have a stored procedure i want to only insert in the database if the record doesnt exists however if exists then only trigger update based on ScheduleID parameter value
thanks
Alter PROCEDURE [dbo].[UpdateSecEventsandSchedle]

	
	@ScheduleID int,
	@ScheduledSlotId int,
	@VideoItemID int,
	@ItemOrder int,
	@VideoStartTime datetime,
	@VideoEndTime datetime,
	@VideoLength datetime ,
	@Userid int,
	@showid int,
	@SecEvent int,
	@SecEventText text 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;

	IF @ScheduleID > 0 
		BEGIN
		UPDATE    SlotVideoItems
SET              VideoItemID = 0
where ScheduleID = 1099
		INSERT INTO SlotVideoItemsevents
           ([ScheduleID]
           ,[ScheduledSlotId]
           ,[VideoItemID]
           ,[ItemOrder]
           ,[VideoStartTime]
           ,[VideoEndTime]
           ,[VideoLength]
           ,[Userid]
           ,[Showid]
           ,[SecEvent] 
	,[SecEventText] )
VALUES     

(@ScheduleID ,
	@ScheduledSlotId ,
	@VideoItemID ,
	@ItemOrder ,
	@VideoStartTime ,
	@VideoEndTime ,
	@VideoLength  ,
	@Userid,
	@showid,
	@SecEvent,
	@SecEventText )
		END
END

Open in new window

0
mattibutt
Asked:
mattibutt
  • 8
  • 7
2 Solutions
 
mmr159Commented:
Use IF EXISTS

IF EXISTS(SELECT * FROM table WHERE criteria)
UPDATE table
ELSE
INSERT INTO table
0
 
mattibuttAuthor Commented:
how do i apply it to the current query
0
 
rushShahCommented:
try this...

Alter PROCEDURE [dbo].[UpdateSecEventsandSchedle]	
	@ScheduleID int,
	@ScheduledSlotId int,
	@VideoItemID int,
	@ItemOrder int,
	@VideoStartTime datetime,
	@VideoEndTime datetime,
	@VideoLength datetime ,
	@Userid int,
	@showid int,
	@SecEvent int,
	@SecEventText text 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;

	IF EXISTS (SELECT ScheduleID FROM SlotVideoItems WHERE ScheduleID=@ScheduleID)
	BEGIN
		UPDATE  SlotVideoItems
		SET		VideoItemID = 0
		where	ScheduleID = @ScheduleID
	END
	ELSE
	BEGIN
		INSERT INTO SlotVideoItemsevents
           ([ScheduleID]
           ,[ScheduledSlotId]
           ,[VideoItemID]
           ,[ItemOrder]
           ,[VideoStartTime]
           ,[VideoEndTime]
           ,[VideoLength]
           ,[Userid]
           ,[Showid]
           ,[SecEvent] 
	,[SecEventText] )
VALUES     
(@ScheduleID ,
	@ScheduledSlotId ,
	@VideoItemID ,
	@ItemOrder ,
	@VideoStartTime ,
	@VideoEndTime ,
	@VideoLength  ,
	@Userid,
	@showid,
	@SecEvent,
	@SecEventText )
		END
END
GO

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
mattibuttAuthor Commented:
hi

i want to be able to update all fields in SlotVideoItemsevents something like if not exists clause

ignore this query what you have done is modified the static query it was there for some other purpose

IF EXISTS (SELECT ScheduleID FROM SlotVideoItems WHERE ScheduleID=@ScheduleID)
      BEGIN
            UPDATE  SlotVideoItems
            SET            VideoItemID = 0
            where      ScheduleID = @ScheduleID

what i want to be able to do is check whether the scheduleid already exists in the SlotVideoItemsevents if it does then update with the same parameters
thanks
0
 
mmr159Commented:
>>what i want to be able to do is check whether the scheduleid already exists in the SlotVideoItemsevents if it does then update with the same parameters <<

Your code does almost exactly that...  All I did was change the FROM table from SlotVideoItems to SlotVideoItemsevents.

IF EXISTS (SELECT ScheduleID FROM SlotVideoItemsevents WHERE ScheduleID=@ScheduleID)
      BEGIN
            UPDATE  SlotVideoItems
            SET            VideoItemID = 0
            where      ScheduleID = @ScheduleID


All you have to do after this is:

END
ELSE
BEGIN
INSERT
END
0
 
mattibuttAuthor Commented:
but i want to update all the fields        SET            VideoItemID = 0 not just this
0
 
mmr159Commented:
Oh, sorry.  The syntax for setting (updating) multiple fields is this:

UPDATE table SET col1=a, col2=b, col3=c
WHERE criteria

All you need to do now is add the rest of the column=val separated by a comma.
0
 
mmr159Commented:
This should do it.

UPDATE SlotVideoItems
      SET VideoItemID = @VideoItemID,
            ItemOrder = @ItemOrder,
            VideoStartTime = @VideoStartTime,
            VideoEndTime = @VideoEndTime,
            VideoLength = @VideoLength,
            Userid = @Userid,
            Showid = @Showid,
            SecEvent  = @SecEvent,
            SecEventText = @SecEventText
WHERE ScheduleID = @ScheduleID
0
 
mattibuttAuthor Commented:
hi
i have just realise something update will not be a viable option so i want to delete all the rows if it matches the criteria problem is when i run the code it just enter one row although no of rows are 75



Alter PROCEDURE [dbo].[UpdateSecEventsandSchedle]

      
      @ScheduleID int,
      @ScheduledSlotId int,
      @VideoItemID int,
      @ItemOrder int,
      @VideoStartTime datetime,
      @VideoEndTime datetime,
      @VideoLength datetime ,
      @Userid int,
      @showid int,
      @SecEvent int,
      @SecEventText text
AS
 
set nocount on


      
 delete   SlotVideoItemsevents WHERE ScheduleID=@ScheduleID


 



     
      INSERT INTO SlotVideoItemsevents
           ([ScheduleID]
           ,[ScheduledSlotId]
           ,[VideoItemID]
           ,[ItemOrder]
           ,[VideoStartTime]
           ,[VideoEndTime]
           ,[VideoLength]
           ,[Userid]
           ,[Showid]
           ,[SecEvent]
      ,[SecEventText] )
VALUES    

(@ScheduleID ,
      @ScheduledSlotId ,
      @VideoItemID ,
      @ItemOrder ,
      @VideoStartTime ,
      @VideoEndTime ,
      @VideoLength  ,
      @Userid,
      @showid,
      @SecEvent,
      @SecEventText )

     
            
            


0
 
mmr159Commented:
Yes this will procedure will delete 0 to many rows and insert only 1.

I assume an update is not viable because you don't want to update many rows with one set of variables.  If this is correct, then you need a unique row identifier.  If ScheduleID X can reference more than one row, we need another column - or - a group a columns that guarantee uniqueness.
0
 
mmr159Commented:
And once you find a way to uniquely identify one row, then you can use UPDATE.
0
 
mattibuttAuthor Commented:
hi
i have just realised that my insertion maybe different each time so for example if i inserted 30 rows then when i am redoing that i might be inserting 40 rows so what i simply wanna do is delete everything associated with the schedule and reinsert them the problem which is very strange is basically its only inserting one row
0
 
mmr159Commented:
It is not strange.  This code

INSERT INTO (a,b,c)
VALUES (x,y,z)

will always insert only one row, never more than one.

If you want to insert multiple rows, you have to either call this statement multiple times, or use a set-based insertion like

INSERT INTO (a,b,c)
SELECT x,y,z
FROM table
WHERE criteria

Is the information you want to insert available from another table?  If not, where are the other new values coming from?  My guess is that your procedure-calling code has this information... you just need to call it as many times as you need, passing the each different lot of values in each time.
0
 
mmr159Commented:
And keep in mind - if you call this over and over again with the same ScheduleID, you will only get one record and the end of processing because you are deleting all schedule ids before each *single row* insert.  So you will have to delete once before you start inserting.

0
 
mattibuttAuthor Commented:
hi
i am inserting via web interface using repeater so it works perfectly fine without the delete statement
0
 
mattibuttAuthor Commented:
thanks guys it turned i had to create another Stored Procedure which deletes values from the table
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now