ColdFusion Muse

Inserting and Updating in a Single Query Statement

Posted At : June 23, 2006 10:20 AM | Posted By : Mark Kruger
Related Categories: MS SQL Server, Coldfusion & Databases

This is a tip about something I do regularly. No, I'm not talking about forgetting to put down the seat. I'm talking about coding. Let's say you have an application that collects user data in a wizard-like format using a few steps. You allow folks to go back and forth between steps and edit what they've done. How do you handle the insert on that first step without creating duplicates when users return to that step? There are several approaches to this problem:

You could store something in the session or in a cookie to indicate the insert has already been done. You could not do an insert after step 1 and simply hold all the form variables till the "finish" button is pressed on the last step. Or... you could do something like this.

<cfquery name="put" datasource="myDsn">
      IF NOT EXISTS
            (SELECT * FROM myTable
             WHERE   email = '#form.email#'
             AND ....)
         BEGIN
            INSERT INTO myTable
               (email, fnmae, lname, ...)
            VALUES
               ('#form.email#',
                '#form.fname#',
                '#form.lname#',
                ...)
         END
      ELSE
         BEGIN
            UPDATE   myTable
            SET      fname = '#form.fname#',
                  lname = '#form.lname#',
            WHERE   email = '#form.Email#'

         END
   </cfquery>

This SQL code checks to see if the user email (assumed to be unique in this example) exists. If it does not exist the statement does an insert. If it does exist the statement does an update (and yes I know if the user changed his email all bets are off).

3 things of note

I did not use <cfqueryparam ...> simply because of space and readability (for this blog). Normally all the form elements would be bound.

Secondly, this query doesn't return anything. If page 2 of your wizard needs an ID from the insert that could be a problem. So you will need to add @@identity or SCOPE_IDENTITY() to the query to get back the ID from the insert or a regular select to get the ID back from the update. That's pretty easy from within our context as well.

<cfquery name="put" datasource="myDsn">
   SET NOCOUNT ON
      IF NOT EXISTS
            (SELECT * FROM myTable
             WHERE   email = '#form.email#'
             AND ....)
         BEGIN
            INSERT INTO myTable
               (email, fnmae, lname, ...)
            VALUES
               ('#form.email#',
                '#form.fname#',
                '#form.lname#',
                ...)
            SELECT SCOPE_IDENTITY() AS newId
         END
      ELSE
         BEGIN
            UPDATE   myTable
            SET      fname = '#form.fname#',
                  lname = '#form.lname#',
            WHERE   email = '#form.Email#'

            SELECT id AS newid
            FROM   myTable
            WHERE   email = '#form.email#'

         END
   SET NOCOUNT OFF
   </cfquery>
   
   <cfset usersId = put.newid>

Finally, this code is specific to MS SQL server. There are ways to do this on other platforms. Perhaps some readers who are mySQL or Oracle gurus can add to the post.

Comments (8) |

Print |

del.icio.us |

Digg It!

Comments

[Add Comment] [Subscribe to Comments]

A useful and somewhat, DOH, tip. I had all but forgotten that we can put complex SQl in cfquery blocks. simply addiing the BEgin + end tags and returning the ID of the record is fully useful in itself. let alone the if exists statement.

Only 1 caution would be the transaction timing. Would this be transactionally safe since its within the BEGIN-END block (the if-exists and the pk returning).

Nice tip

# Posted By Brandon Wittwer | 6/23/06 8:56 AM

If your server is set to implicit commit (as it is by default) I believe this would be safe. I'll check on that...

# Posted By mkruger | 6/23/06 9:05 AM

One thing I would recommend is to do an update first, then if it returns 0 rows, do the insert. This way, the most queries you will have is 2. If the record already exists, only the update will run.

e.g.

UPDATE myTable
SET fname = '#form.fname#',
lname = '#form.lname#',
WHERE email = '#form.Email#'

IF @@rowcount = 0 BEGIN
INSERT INTO myTable
(email, fnmae, lname, ...)
VALUES
('#form.email#',
'#form.fname#',
'#form.lname#',
...)
END

# Posted By Mario Rodrigues | 6/23/06 10:49 AM

@Mario,

aaahhhh I don't think that you want to do that there Mario. By doing an update, you put a locks on indexes and tables that are unnescessary not to mention writing to the transaction logs. Mark has the right idea using the EXISTS check (by the... bravo for using EXISTS. you have no idea how many times I see SELECT * and then if checking @@ROWCOUNT = 1).

# Posted By Tony Petruzzi | 6/23/06 2:06 PM

mario and Tom,

I see mario's point - 1 statement is better than 2. But I think I buy Tony's argument first (plus he's so flattering - ha). I think that there would be less of a penalty for the IF EXISTS than for the update + optional insert. I wonder if the execution plan in Querey Analyzer would show that clearly. I'll have to check.

# Posted By Mkruger | 6/23/06 2:10 PM

For inserts/updates conditional on a primary key or unique index, MySQL 4.1+ provides beautifully concise syntax:

INSERT INTO myTable
(email, fname, lname)
VALUES
('#form.email#',
'#form.fname#',
'#form.lname#')
ON DUPLICATE KEY UPDATE
email = '#form.email#',
fname = '#form.fname#',
lname = '#form.lname#'

Where 'email' is the PK or at least has a unique index.

# Posted By Julian | 6/26/06 5:37 AM

Thanks Mark I was able to put this to use! =)