postmodern-update

Postmodern Examples Table of Contents Insert

Update

In s-sql, updates operate the way you would expect them to, given what we have seen up above.

(query (:update 'countries :set 'text '$1 :where (:= 'id 284)) "now")

Now temporarily assume that we do not have a normalized database and we have a field "region_name" in the countries table in the database and a slot accessor named region-name in the countries class.

We could update a set of the countries rows to get the regional names for a particular set of countries, given a list of countries.id as follows:

(query

 (:update 'countries

          :set 'region-name

          (:select 'name :from 'regions

                   :where (:= 'countries.id 'country-id))

          :where (:in 'countries.id

                      (:set 129 139 132 128 135 134 131 137 130 133 136))))

Assume you wanted to update a record with id=5 and you had a plist of the the columns to be updated.

Assuming you wanted to create something reusable, you could use a query like the following:

(query

 (sql-compile (append (append `(:update ,table :set)

                              plst)

                      `(:where (:= 'id ,id)))))

You can use the RETURNING keyword to return all or parts of the updated entries.

(query (sql (:update 'weather

             :set 'temp-lo (:+ 'temp-lo 1) 'temp-hi (:+ 'temp-lo 15) 'prcp :default

             :where (:and (:= 'city "San Francisco")

                          (:= 'date "2003-07-03"))

             :returning 'temp-lo 'temp-hi 'prcp))

Alternative Column list Syntax (Requires Postmodern v.1.32.8)

Use the alternative column-list syntax to do the same update:

(query (:update 'weather

        :columns 'temp-lo 'temp-hi 'prcp

        (:set (:+ 'temp-lo 1)  (:+ 'temp-lo 15)  :DEFAULT)

         :where (:and (:= 'city "San Francisco")

                      (:= 'date "2003-07-03"))))

Single New Value for Multiple Rows

When you need to update lots of rows, a single call to the database is often more efficient, but what that call looks like will depend on your data. If you have a single value that needs to be inserted into multiple rows, you just need to manage the condition clause. To create a silly example, suppose we want to change the intermediate_region_name in the regions table to "Too Close to the UK" instead of "Channel Islands". Here are three different ways to do that

(query (:update 'regions

        :set 'intermediate-region-name "Too Close to the UK"

        :where (:= 'intermediate-region-name "Channel Islands")))

(query (:update 'regions

        :set 'intermediate-region-name "Too Close to the UK"

        :where (:in 'id (:set 179 180))))

(query (:update 'regions

        :set 'intermediate-region-name "Too Close to the UK"

        :where (:or (:= 'country "Guernsey")

                    (:= 'country "Jersey"))))

Using a Case Statement

If you have a limited number of situations with a different value for each situation, you can reach for a case statement.

Staying with silly renames of intermediate_region_names, suppose we want "Caribbean" to be "Warm Island Americas" (thus excluding Bermuda) and "Central America" to be "Connecting Bridge Americas". One form of the case statement accomplishing this could look like this:

(query (:update 'regions

        :set 'intermediate-region-name

              (:case ((:= 'intermediate-region-name "Caribbean")

                       "Warm Island Americas")

                     ((:= 'intermediate-region-name "Central America")

                       "Connecting Bridge Americas"))

         :where (:in 'intermediate-region-name

                   (:set "Caribbean" "Central America"))))

Pulling Updated Info From Another Table

(query (:update 'geo

        :set 'iso3 'regions.iso3

        :from 'regions

        :where (:= 'regions.iso2 'geo.iso3)))

upsert or "On Conflict"

Some people use the term "upsert" for trying to insert a new row, but if that record already exists, then either update the row with new values or do nothing (as opposed to throwing an error).

Beginning in Postgresql versions 9.5 and above, it is possible to use what Postgresql calls on-conflict. There are two versions - "on conflict do nothing" or "on conflict update". See below for sample call in postmodern for on-conflict-update.

(query (:insert-into 'test-table :set 'column-A '$1 'column-B '$2

                     :on-conflict-update 'column-A

                     :update-set 'column-B '$2

                     :where (:= 'test-table.column-A '$1)) "c" 37)

(query (:select 'countries.name :from 'countries 'regions

                               :where (:and (:or (:= 'regions.name "North America")

                                            (:= 'regions.name "Central America"))

                                             (:= 'regions.id 'countries.region-id))))

Delete