| Author |
Topic |
|
shapper
Constraint Violating Yak Guru
450 Posts |
 Posted - 2007-09-14 : 12:48:55
|
| Hello,I have a procedure which inputs are @id, @title and @body.Basically I need to insert a new record if there is no record with the given @id or update the record if one is found with the given @id.My question is:can be this done without using IF?Just like a mixture of Insert/UpdateThanks,Miguel |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-09-14 : 12:55:50
|
| http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-14 : 12:56:42
|
| no, you need the ifisn't this similar to your other problem on categories and articles?--------------------keeping it simple... |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-14 : 13:11:21
|
| nice... what does the execution plan look like? i expect similar to CASE as to IF--------------------keeping it simple... |
|
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2007-09-14 : 13:11:33
|
| Hi Jen,No, this is just a generic question of how to update if exists and not if exists for a rating system. I have this working with an IF. I was just wondering if it could be done in another way.Going to read the recommended blogs.Thanks,Miguel |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-14 : 13:13:19
|
ah i see... nice to hear you had it sorted out cheers--------------------keeping it simple... |
|
|
|
shapper
Constraint Violating Yak Guru
450 Posts |
Posted - 2007-09-14 : 13:55:06
|
| When will SQL 2008 come out?Will it be at the same time that VS 2008?Thanks,Miguel |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-09-14 : 14:02:55
|
| yup.VS 2008, SQL Server 2008 and Windows Server 2008 are all said to be released on 27th Feb 2008 if memory serves_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-14 : 22:06:26
|
"No, this is just a generic question of how to update if exists and not if exists for a rating system. I have this working with an IF. I was just wondering if it could be done in another way."One way is to do the INSERT and if that gives you 0 rows (i.e. it failed!) then you do the UPDATE instead.Or do it the other way round. (Choose whichever is the more likely (New record or Existing record) as the first method, so that it more frequently succeeds).We do this by Sproc and we pass an "Edit Version" to the Sproc.Each table contains an "Edit Version" column. For us it is an INT that gets incremented on every save, but you could use a ROWVERSION datatype (which will guarantee that SQL Server itself changes it on an insert/update, which may be safer than remembering to increment an INT column!)Anyway, we do something like:UPDATE MyTableSET MyCol1 = ...WHERE MyPK = 'FOO' AND MyEditVersion = 1234 that way if the EditVersion for that row is NOT 1234 the update will change 0 rows, and that's an error - someone else has already changed the row and beat me to it.OK, so now we have an SProc that expects an EditVersion.We have some special values for EditVersion:N - the edit version we expect the row to be. The row must exists. This is an UPDATE operation.0 - Special value implying an INSERT. The row must NOT already exist.NULL - Don't care. Update if the row exists, otherwise Insert. (This cause an EXISTS to be done to discover if the row exists, and what its EditVersion currently is, and then proceeds as above)-1 - Don't care what the EditVersion is, but the record MUST exist. Similar to NULL above.We work on the basis that most times the application knows whether a record exists or not, so most times the Sproc just does a single INSERT or UPDATE (yes, based on a an IF!), and that is pretty efficient in practice.Also, having the INSERT and UPDATE in a single SProc we think make maintenance more robust - all changes for either scenario are in a single Sproc, and if you change the Update part you are likely to remember to change the Insert part too!Kristen |
|
|
|
|
Insert/Update