Inserting and Updating in a Single Query Statement
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.
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>
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.
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>
<!--- set a variable to the id returned --->
<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.
8 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 -
If your server is set to implicit commit (as it is by default) I believe this would be safe. I'll check on that... -
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 -
@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). -
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. -
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. -
I was hoping some oracle gurus would provide a syntax for this functionality... Any takers? ")
Sites to Watch
Aggregators
RSS
