Update/Insert question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update/Insert question

Author

Topic

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-10-31 : 11:28:43

what is the best/correct way about testing if a particular row(entry) exists in a table and if it does update it and if it doesn't then insert one?

Thanx

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-31 : 11:34:28

you can do


if not exist (select * from MyTable where yourConditions)
    then insert here

i usualy have 1 insert and 1 update stored procedure for that scenario.
But if you have to do it like this then i do it like:


update MyTable
set ...
where yourConditions

if @@rowcount = 0
begin
   insert into ....
end

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-10-31 : 11:37:43

hvala majstore :-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-31 : 11:42:12

you're welcome

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-10-31 : 11:52:46

question: can you have an if else statement is a stored procedure? getting a syntax error at the first BEGIN


CREATE PROCEDURE UpdateFilePath 
	-- Add the parameters for the stored procedure here
	@fPath as varchar(max), 
	@fMachineID as uniqueidentifier
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF exists
    (select * from hwk_filepaths where fmachineID = @fMachineID)
		BEGIN 
			UPDATE hwk_filepaths 
			SET fPath=@fPath
			WHERE  fMachineID = @fMachineID
		END
	ELSE
		BEGIN
			INSERT INTO hwk_filepaths (fPath,fMachineID)
			VALUES (@fPath,@fMachineID)
		END
END

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-31 : 11:57:18

your problem isn't in the if else part. You're missing AS


CREATE PROCEDURE UpdateFilePath 
	-- Add the parameters for the stored procedure here
	@fPath as varchar(max), 
	@fMachineID as uniqueidentifier
AS
BEGIN
   ...
END

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-31 : 11:58:12

i do prefer

update MyTable
set ...
where yourConditions

if @@rowcount = 0
begin
insert into ....
end

over exists beacues there's no select.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2006-10-31 : 11:59:33

hvala majstore :-)

I sign for fame not for shame but all the same, I sign my name.

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-10-31 : 12:03:15

stupid is as stupid does. Note to self: when deleting things, don't delete what you need :-)

why the preference for no select?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-31 : 12:11:04