You are viewing documentation for v1.0.x. Change

Updating Records

Updating records in your database tables.

When you have created or retrieved an object, you can save it to the database by calling its save() method. This method returns true if the object passes all validations and the object was saved to the database. Otherwise, it returns false.

This chapter will focus on how to update records. Read the Creating Records chapter for more information about how to create new records.

A Practical Example

Let's start with an example of getting a blog post from the database, updating its title, and saving it back:

<cfset post = model("post").findByKey(33)>
<cfset post.title = "New version of Wheels just released">
<cfset post.save()>

You can also change the values of one or more properties and save them to the database in one single call using the update() method, like this:

<cfset post = model("post").findByKey(33)>
<cfset post.update(title="New version of Wheels just released")>

Updating Via struct Values

You can also pass in name/value pairs to update() as a struct. The main reason this method accepts a struct is to allow you to easily use it with forms.

This is how it would look if you wanted to update the properties for a post based on a submitted form.

<cfset post = model("post").findByKey(params.key)>
<cfset post.update(params.post)>

It's also possible to combine named arguments with a struct, but then you need to name the struct argument as properties.

Example:

<cfset post = model("post").findByKey(params.key)>
<cfset
post.update(
title="New version of Wheels just released", properties=params.post
)
>

Combine Reading and Updating into a Single Call

To cut down even more on lines of code, you can also combine the reading and saving of the objects by using the class-level methods updateByKey() and updateAll().

The updateByKey() Method

Give the updateByKey() method a primary key value (or several if you use composite keys) in the key argument, and it will update the corresponding record in your table with the properties you give it. You can pass in the properties either as named arguments or as a struct to the properties argument.

This method returns the object with the primary key value you specified. If the object does not pass validation, it will be returned anyway, but nothing will be saved to the database.

By default, updateByKey() will fetch the object first and call the update() method on it, thus invoking any callbacks and validations you have specified for the model. You can change this behavior by passing in instantiate=false. Then it will just update the record from the table using a simple UPDATE query.

An example of using updateByKey() by passing a struct:

<cfset result = model("post").updateByKey(33, params.post)>

And an example of using updateByKey() by passing named arguments:

<cfset
result = model("post").updateByKey(
id=33, title="New version of Wheels just released", published=1
)
>

Updating Multiple Rows with updateAll()

The updateAll() method allows you to update more than one record in a single call. You specify what records to update with the where argument and tell Wheels what updates to make using named arguments for the properties.

The where argument is used exactly as you specify it in the WHERE clause of the query (with the exception that Wheels automatically wraps everything properly in cfqueryparam tags). So make sure that you place those commas and quotes correctly!

An example:

<cfset
recordsReturned = model("post").updateAll(
published=1, publishedAt=Now(), where="published=0"
)
>

Unlike updateByKey(), the updateAll() method will not instantiate the objects by default. That could be really slow if you wanted to update a lot of records at once.

^ Top
Table of Contents

Comments

Read and submit questions, clarifications, and corrections about this chapter.

[Add Comment]

  1. Joe's Gravatar Joe says:

    Hello - I'm a little confused about how to use the save method when passing a struct from a form submit:

    <---Person struct passed from a form ---->
    <cfset person=model("person").new(params.person) >

    In my situation, the person's "id" attribute is passed in the params struct.  I would assume that wheels knows that the object exists by virtue of the fact that "id" exists in the params struct.  

    <cfset person.save()>

    When executing the save() method I am getting DB SQL error that the database can't insert explicit values for "ID" when identity_insert is OFF.

    I commented out the "id"  textField in the associated view so that "id" was not included in the params struct, and the save method inserted a row to the person table.  

    Is UpdateByKey() my only choice?   Seems to me that it would be pretty simple to use the save() method to create or update a record if the primary key was included in the parameter structure.

    I am working on a method called "CreateOrUpdate" which is why I'm asking.  For now I'll check propertyExists("id") and update when true else create.

    Wheels rocks by the way.

  2. Jacob's Gravatar Jacob says:

    I'm running into the same issue as Joe. When I call the Save() method from an Instance where the property ID is empty, it seems to try an update and throws: Invalid data '' for CFSQLTYPE CF_SQL_INTEGER. I had assumed that Save() would check on ID property and, when empty, run an insert.

    Likewise, when I call Save() and the ID property is an existing ID in the database, I get: "Cannot insert explicit value for identity column in table 'tests' when IDENTITY_INSERT is set to OFF. "

    But I agree, wheels rocks.

  3. Michael's Gravatar Michael says:

    There's a bug in your code for "updateByKey" when using named arguments.

    "id=33" should instead be "key=33"

    From this:

    <cfset
    result = model("post").updateByKey(
    id=33, title="New version of Wheels just released", published=1
    )
    >

    To this...

    <cfset
    result = model("post").updateByKey(
    key=33, title="New version of Wheels just released", published=1
    )
    >

Add Comment