Whether to DELETE and UPDATE - 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)

Whether to DELETE and UPDATE

Author

Topic

mark1504
Posting Yak Master

103 Posts

Posted - 2004-10-12 : 10:57:55

What's the best practice for this situation and why?

I have a record to insert or update (depending on whether it exists).

So, should I?

a)
IF EXISTS (SELECT * FROM MyTable WHERE Thisvar=@thisvar)
UPDATE MyTable ...
ELSE
INSERT INTO MyTable ....

OR...

b)
DELETE FROM MyTable WHERE Thisvar=@Thisvar
INSERT INTO MyTable...

Thanks
Mark

sweko
Starting Member

5 Posts

Posted - 2004-10-12 : 11:10:35

I Prefer using a insert/update logic, since that way you need fewer I/O operations, and usualy, you really need to update some columns, where maintaining values entered in the other columns.
Anyway, the second approach is the Q&D (quick & dirty) way to do the same thing - less coding, less room for bugs :)

IMHO, i would go with the first way in a SP that's supposed to stick around,
but i would use the second way for some one-off i'll run from QA.

--
SWeko has spoken

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-12 : 11:25:10

quote:

a)
IF EXISTS (SELECT * FROM MyTable WHERE Thisvar=@thisvar)
UPDATE MyTable ...
ELSE
INSERT INTO MyTable ....

lose the IF EXISTS -- you don't need it. just do the update. then if the rowcount = 0, do the insert.

remember you cannot delete and then insert if other tables are referencing the row you remove. in that case you must update.

- Jeff

mark1504
Posting Yak Master

103 Posts

Posted - 2004-10-12 : 11:36:03

Jeff

Can you explain more about Rowcount=0.

When I use a stored procedure I thought attempting to update a record that didn't exist causes the SP to error out.

Thanks
Mark

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-12 : 11:41:18

no, just no rows are updated.

UPDATE Table SET Status="Active" WHERE CustomerID = "XXXXXX"

if that customerID doesn't exist, the update runs fine, but affects 0 rows. Read up on ROWCOUNT in books on-line. better yet, try this yourself and see what happens. Experiment, play around, create a test database and a test table and try different methods to see what works. never do your testing and learning of methods like this on actual data. when in doubt, try it! that's the best way to learn.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-12 : 12:11:33

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(50))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'Whos' UNION ALL
SELECT 2, 'Your' UNION ALL
SELECT 3, 'Daddy'
GO

SELECT * FROM myTable99
GO

CREATE PROC mySproc99 @Col1 int, @Col2 char(50)
AS
DECLARE @rowcount int
UPDATE myTable99 SET Col2 = @Col2 WHERE Col1 = @Col1
SET @rowcount = @@ROWCOUNT
IF @rowcount = 0
INSERT INTO myTable99(Col1,Col2) SELECT @Col1, @Col2

GO

DECLARE @Col1 int, @Col2 char(50)
SELECT @Col1 = 1, @Col2 = 'Who''s'
EXEC mySproc99 @Col1, @Col2

SELECT * FROM myTable99
GO

DECLARE @Col1 int, @Col2 char(50)
SELECT @Col1 = 4, @Col2 = 'Now'
EXEC mySproc99 @Col1, @Col2

SELECT * FROM myTable99
GO

SET NOCOUNT ON
DROP PROC mySproc99
DROP TABLE myTable99
GO

[/code]

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-10-12 : 15:30:04

An additional 2p ...

If your record has a number of indexed columns, and the columns which are indexed have not changed, then an UPDATE is going to be much quicker than a DELETE/INSERT

Kristen

mark1504
Posting Yak Master

103 Posts

Posted - 2004-10-12 : 15:40:07

Thanks everybody. Very useful information.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources