Solved

mssql update if already exists

Posted on 2010-09-23
16
613 Views
Last Modified: 2012-05-10
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
Comment
Question by:mattibutt
  • 8
  • 7
16 Comments
 
LVL 7

Accepted Solution

by:
mmr159 earned 250 total points
ID: 33746060
Use IF EXISTS

IF EXISTS(SELECT * FROM table WHERE criteria)
UPDATE table
ELSE
INSERT INTO table
0
 
LVL 11

Author Comment

by:mattibutt
ID: 33746644
how do i apply it to the current query
0
 
LVL 8

Assisted Solution

by:rushShah
rushShah earned 250 total points
ID: 33746825
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
 
LVL 11

Author Comment

by:mattibutt
ID: 33747049
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
 
LVL 7

Expert Comment

by:mmr159
ID: 33747361
>>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
 
LVL 11

Author Comment

by:mattibutt
ID: 33748415
but i want to update all the fields        SET            VideoItemID = 0 not just this
0
 
LVL 7

Expert Comment

by:mmr159
ID: 33748962
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
 
LVL 7

Expert Comment

by:mmr159
ID: 33749005
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 11

Author Comment

by:mattibutt
ID: 33752382
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
 
LVL 7

Expert Comment

by:mmr159
ID: 33753558
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
 
LVL 7

Expert Comment

by:mmr159
ID: 33753568
And once you find a way to uniquely identify one row, then you can use UPDATE.
0
 
LVL 11

Author Comment

by:mattibutt
ID: 33753772
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
 
LVL 7

Expert Comment

by:mmr159
ID: 33754404
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
 
LVL 7

Expert Comment

by:mmr159
ID: 33754441
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
 
LVL 11

Author Comment

by:mattibutt
ID: 33754463
hi
i am inserting via web interface using repeater so it works perfectly fine without the delete statement
0
 
LVL 11

Author Closing Comment

by:mattibutt
ID: 33754807
thanks guys it turned i had to create another Stored Procedure which deletes values from the table
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now