Solved

mssql update if already exists

Posted on 2010-09-23
16
619 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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
 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
migration MS SQL database to Oracle 30 59
best counters for cpu high usage 3 21
SQL Update trigger 5 16
Find results from sql within a time span 11 29
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

815 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

8 Experts available now in Live!

Get 1:1 Help Now