Skip to content

UPDATE

SQL UPDATE expressions allow you to update rows in the database.

Beam supplies two functions to update a row in a beam database.

Saving entire rows

The save function allows you to save entire rows to a database. It generates a SET clause that sets the value of every non-primary-key column and a WHERE clause that matches on the primary key.

For example, suppose we have a customer object representing Mark Philips

let c :: Customer
    c = Customer 14 "Mark" "Philips" (Just "Telus")
                 (Address (Just "8210 111 ST NW") (Just "Edmonton") (Just "AB")
                          (Just "Canada") (Just "T6G 2C7"))
                 (Just "+1 (780) 434-4554")
                 (Just "+1 (780) 434-5565")
                 "mphilips12@shaw.ca" (EmployeeKey (Just 5))

Mark's phone number recently changed and we'd like to update the database based on our new customer object. We can use Haskell record update syntax to easily save the entire row.

Just c <- runSelectReturningOne $ lookup_ (customer chinookDb) (CustomerId 14)
putStrLn ("Old phone number is " ++ show (customerPhone c))

runUpdate $
  save (customer chinookDb)
       (c { customerPhone = Just "+1 (123) 456-7890" })

Just c' <- runSelectReturningOne $ lookup_ (customer chinookDb) (CustomerId 14)
putStrLn ("New phone number is " ++ show (customerPhone c'))
SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ("t0"."CustomerId")=(?);

-- With values: [SQLInteger 14];
-- Output: Old phone number is Just "+1 (780) 434-4554"

UPDATE "Customer"
SET "FirstName"=?,
    "LastName"=?,
    "Company"=?,
    "Address"=?,
    "City"=?,
    "State"=?,
    "Country"=?,
    "PostalCode"=?,
    "Phone"=?,
    "Fax"=?,
    "Email"=?,
    "SupportRepId"=?
WHERE ("CustomerId")=(?);

-- With values: [SQLText "Mark",SQLText "Philips",SQLText "Telus",SQLText "8210 111 ST NW",SQLText "Edmonton",SQLText "AB",SQLText "Canada",SQLText "T6G 2C7",SQLText "+1 (123) 456-7890",SQLText "+1 (780) 434-5565",SQLText "mphilips12@shaw.ca",SQLInteger 5,SQLInteger 14];

SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ("t0"."CustomerId")=(?);

-- With values: [SQLInteger 14];
-- Output: New phone number is Just "+1 (123) 456-7890"
SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ("t0"."CustomerId") = (14);

-- Output: Old phone number is Just "+1 (780) 434-4554"

UPDATE "Customer"
SET "FirstName"='Mark',
    "LastName"='Philips',
    "Company"='Telus',
    "Address"='8210 111 ST NW',
    "City"='Edmonton',
    "State"='AB',
    "Country"='Canada',
    "PostalCode"='T6G 2C7',
    "Phone"='+1 (123) 456-7890',
    "Fax"='+1 (780) 434-5565',
    "Email"='mphilips12@shaw.ca',
    "SupportRepId"=5
WHERE ("CustomerId") = (14);


SELECT "t0"."CustomerId" AS "res0",
       "t0"."FirstName" AS "res1",
       "t0"."LastName" AS "res2",
       "t0"."Company" AS "res3",
       "t0"."Address" AS "res4",
       "t0"."City" AS "res5",
       "t0"."State" AS "res6",
       "t0"."Country" AS "res7",
       "t0"."PostalCode" AS "res8",
       "t0"."Phone" AS "res9",
       "t0"."Fax" AS "res10",
       "t0"."Email" AS "res11",
       "t0"."SupportRepId" AS "res12"
FROM "Customer" AS "t0"
WHERE ("t0"."CustomerId") = (14);

-- Output: New phone number is Just "+1 (123) 456-7890"
SELECT `t0`.`CustomerId` AS `res0`,
       `t0`.`FirstName` AS `res1`,
       `t0`.`LastName` AS `res2`,
       `t0`.`Company` AS `res3`,
       `t0`.`Address` AS `res4`,
       `t0`.`City` AS `res5`,
       `t0`.`State` AS `res6`,
       `t0`.`Country` AS `res7`,
       `t0`.`PostalCode` AS `res8`,
       `t0`.`Phone` AS `res9`,
       `t0`.`Fax` AS `res10`,
       `t0`.`Email` AS `res11`,
       `t0`.`SupportRepId` AS `res12`
FROM `Customer` AS `t0`
WHERE (`t0`.`CustomerId`) = (14);

-- Output: Old phone number is Just "+1 (780) 434-4554"

UPDATE `Customer`
SET `FirstName`='Mark',
    `LastName`='Philips',
    `Company`='Telus',
    `Address`='8210 111 ST NW',
    `City`='Edmonton',
    `State`='AB',
    `Country`='Canada',
    `PostalCode`='T6G 2C7',
    `Phone`='+1 (123) 456-7890',
    `Fax`='+1 (780) 434-5565',
    `Email`='mphilips12@shaw.ca',
    `SupportRepId`=5
WHERE (`CustomerId`) = (14);


SELECT `t0`.`CustomerId` AS `res0`,
       `t0`.`FirstName` AS `res1`,
       `t0`.`LastName` AS `res2`,
       `t0`.`Company` AS `res3`,
       `t0`.`Address` AS `res4`,
       `t0`.`City` AS `res5`,
       `t0`.`State` AS `res6`,
       `t0`.`Country` AS `res7`,
       `t0`.`PostalCode` AS `res8`,
       `t0`.`Phone` AS `res9`,
       `t0`.`Fax` AS `res10`,
       `t0`.`Email` AS `res11`,
       `t0`.`SupportRepId` AS `res12`
FROM `Customer` AS `t0`
WHERE (`t0`.`CustomerId`) = (14);

-- Output: New phone number is Just "+1 (123) 456-7890"

The save function generates a value of type SqlUpdate syntax CustomerT, where syntax is the type of the appropriate backend syntax (SqliteCommandSyntax for beam-sqlite and PgCommandSyntax for beam-postgres). Like select and the runSelect* functions, we use the runUpdate function to run the command against the database

Fine-grained updates

While save is useful when many fields may have changed, often times you only want to update a few columns. Moreover, if you have several large columns, using save may end up sending huge pieces of data to the database. The SQL UPDATE syntax allows you to set or modify each column individually and to even calculate a new value based off the result of an expression.

The beam update function exposes this functionality. update takes a table, a set of assignments, and a boolean expression, and returns a SqlUpdate.

For example, suppose Canada and the USA became one country and we needed to update all customer addresses to reflect that.

Just canadianCount <-
  runSelectReturningOne $ select $
  aggregate_ (\_ -> as_ @Int countAll_) $
  filter_ (\c -> addressCountry (customerAddress c) ==. val_ (Just "Canada")) $
  all_ (customer chinookDb)
Just usaCount <-
  runSelectReturningOne $ select $
  aggregate_ (\_ -> as_ @Int countAll_) $
  filter_ (\c -> addressCountry (customerAddress c) ==. val_ (Just "USA")) $
  all_ (customer chinookDb)
putStrLn ("Before, there were " ++ show canadianCount ++ " addresses in Canada and " ++ show usaCount ++ " in the USA.")

-- This is the important part!
runUpdate $ update (customer chinookDb)
                   (\c -> [ addressCountry (customerAddress c) <-. val_ (Just "USA") ])
                   (\c -> addressCountry (customerAddress c) ==. val_ (Just "Canada"))

Just canadianCount' <-
  runSelectReturningOne $ select $
  aggregate_ (\_ -> as_ @Int countAll_) $
  filter_ (\c -> addressCountry (customerAddress c) ==. val_ (Just "Canada")) $
  all_ (customer chinookDb)
Just usaCount' <-
  runSelectReturningOne $ select $
  aggregate_ (\_ -> as_ @Int countAll_) $
  filter_ (\c -> addressCountry (customerAddress c) ==. val_ (Just "USA")) $
  all_ (customer chinookDb)
putStrLn ("Now, there are " ++ show canadianCount' ++ " addresses in Canada and " ++ show usaCount' ++ " in the USA.")
SELECT COUNT(*) AS "res0"
FROM "Customer" AS "t0"
WHERE CASE
          WHEN (("t0"."Country") IS NULL)
               AND ((?) IS NULL) THEN ?
          WHEN (("t0"."Country") IS NULL)
               OR ((?) IS NULL) THEN ?
          ELSE ("t0"."Country")=(?)
      END;

-- With values: [SQLText "Canada",SQLInteger 1,SQLText "Canada",SQLInteger 0,SQLText "Canada"];

SELECT COUNT(*) AS "res0"
FROM "Customer" AS "t0"
WHERE CASE
          WHEN (("t0"."Country") IS NULL)
               AND ((?) IS NULL) THEN ?
          WHEN (("t0"."Country") IS NULL)
               OR ((?) IS NULL) THEN ?
          ELSE ("t0"."Country")=(?)
      END;

-- With values: [SQLText "USA",SQLInteger 1,SQLText "USA",SQLInteger 0,SQLText "USA"];
-- Output: Before, there were 8 addresses in Canada and 13 in the USA.

UPDATE "Customer"
SET "Country"=?
WHERE CASE
          WHEN (("Country") IS NULL)
               AND ((?) IS NULL) THEN ?
          WHEN (("Country") IS NULL)
               OR ((?) IS NULL) THEN ?
          ELSE ("Country")=(?)
      END;

-- With values: [SQLText "USA",SQLText "Canada",SQLInteger 1,SQLText "Canada",SQLInteger 0,SQLText "Canada"];

SELECT COUNT(*) AS "res0"
FROM "Customer" AS "t0"
WHERE CASE
          WHEN (("t0"."Country") IS NULL)
               AND ((?) IS NULL) THEN ?
          WHEN (("t0"."Country") IS NULL)
               OR ((?) IS NULL) THEN ?
          ELSE ("t0"."Country")=(?)
      END;

-- With values: [SQLText "Canada",SQLInteger 1,SQLText "Canada",SQLInteger 0,SQLText "Canada"];

SELECT COUNT(*) AS "res0"
FROM "Customer" AS "t0"
WHERE CASE
          WHEN (("t0"."Country") IS NULL)
               AND ((?) IS NULL) THEN ?
          WHEN (("t0"."Country") IS NULL)
               OR ((?) IS NULL) THEN ?
          ELSE ("t0"."Country")=(?)
      END;

-- With values: [SQLText "USA",SQLInteger 1,SQLText "USA",SQLInteger 0,SQLText "USA"];
-- Output: Now, there are 0 addresses in Canada and 21 in the USA.
SELECT COUNT(*) AS "res0"
FROM "Customer" AS "t0"
WHERE ("t0"."Country") IS NOT DISTINCT
  FROM ('Canada');


SELECT COUNT(*) AS "res0"
FROM "Customer" AS "t0"
WHERE ("t0"."Country") IS NOT DISTINCT
  FROM ('USA');

-- Output: Before, there were 8 addresses in Canada and 13 in the USA.

UPDATE "Customer"
SET "Country"='USA'
WHERE ("Country") IS NOT DISTINCT
  FROM ('Canada');


SELECT COUNT(*) AS "res0"
FROM "Customer" AS "t0"
WHERE ("t0"."Country") IS NOT DISTINCT
  FROM ('Canada');


SELECT COUNT(*) AS "res0"
FROM "Customer" AS "t0"
WHERE ("t0"."Country") IS NOT DISTINCT
  FROM ('USA');

-- Output: Now, there are 0 addresses in Canada and 21 in the USA.
SELECT COUNT(*) AS `res0`
FROM `Customer` AS `t0`
WHERE CASE
          WHEN ((`t0`.`Country`) IS NULL)
               AND (('Canada') IS NULL) THEN TRUE
          WHEN ((`t0`.`Country`) IS NULL)
               OR (('Canada') IS NULL) THEN FALSE
          ELSE (`t0`.`Country`) = ('Canada')
      END;


SELECT COUNT(*) AS `res0`
FROM `Customer` AS `t0`
WHERE CASE
          WHEN ((`t0`.`Country`) IS NULL)
               AND (('USA') IS NULL) THEN TRUE
          WHEN ((`t0`.`Country`) IS NULL)
               OR (('USA') IS NULL) THEN FALSE
          ELSE (`t0`.`Country`) = ('USA')
      END;

-- Output: Before, there were 8 addresses in Canada and 13 in the USA.

UPDATE `Customer`
SET `Country`='USA'
WHERE CASE
          WHEN ((`Country`) IS NULL)
               AND (('Canada') IS NULL) THEN TRUE
          WHEN ((`Country`) IS NULL)
               OR (('Canada') IS NULL) THEN FALSE
          ELSE (`Country`) = ('Canada')
      END;


SELECT COUNT(*) AS `res0`
FROM `Customer` AS `t0`
WHERE CASE
          WHEN ((`t0`.`Country`) IS NULL)
               AND (('Canada') IS NULL) THEN TRUE
          WHEN ((`t0`.`Country`) IS NULL)
               OR (('Canada') IS NULL) THEN FALSE
          ELSE (`t0`.`Country`) = ('Canada')
      END;


SELECT COUNT(*) AS `res0`
FROM `Customer` AS `t0`
WHERE CASE
          WHEN ((`t0`.`Country`) IS NULL)
               AND (('USA') IS NULL) THEN TRUE
          WHEN ((`t0`.`Country`) IS NULL)
               OR (('USA') IS NULL) THEN FALSE
          ELSE (`t0`.`Country`) = ('USA')
      END;

-- Output: Now, there are 0 addresses in Canada and 21 in the USA.

We can update columns based on their old value as well, using the current_ function. For example, suppose we wanted to fudge our sales data a bit and double quantity of every line item.

Just totalLineItems <-
  runSelectReturningOne $ select $
  aggregate_ (\ln -> sum_ (invoiceLineQuantity ln)) $
  all_ (invoiceLine chinookDb)
putStrLn ("Before, we had " ++ show totalLineItems ++ " total products sold\n")

runUpdate $ update (invoiceLine chinookDb)
                   (\ln -> [ invoiceLineQuantity ln <-. current_ (invoiceLineQuantity ln) * 2 ])
                   (\_ -> val_ True)

Just totalLineItems' <-
  runSelectReturningOne $ select $
  aggregate_ (\ln -> sum_ (invoiceLineQuantity ln)) $
  all_ (invoiceLine chinookDb)
putStrLn ("With a few simple lines, we've double our sales figure to " ++ show totalLineItems' ++ " products sold!")
SELECT SUM("t0"."Quantity") AS "res0"
FROM "InvoiceLine" AS "t0";

-- With values: [];
-- Output: Before, we had Just 2240 total products sold

UPDATE "InvoiceLine"
SET "Quantity"=("Quantity") * (?)
WHERE ?;

-- With values: [SQLInteger 2,SQLInteger 1];

SELECT SUM("t0"."Quantity") AS "res0"
FROM "InvoiceLine" AS "t0";

-- With values: [];
-- Output: With a few simple lines, we've double our sales figure to Just 4480 products sold!
SELECT SUM("t0"."Quantity") AS "res0"
FROM "InvoiceLine" AS "t0";

-- Output: Before, we had Just 2240 total products sold

UPDATE "InvoiceLine"
SET "Quantity"=("Quantity") * (2)
WHERE true;


SELECT SUM("t0"."Quantity") AS "res0"
FROM "InvoiceLine" AS "t0";

-- Output: With a few simple lines, we've double our sales figure to Just 4480 products sold!
SELECT SUM(`t0`.`Quantity`) AS `res0`
FROM `InvoiceLine` AS `t0`;

-- Output: Before, we had Just 2240 total products sold

UPDATE `InvoiceLine`
SET `Quantity`=(`Quantity`) * (2)
WHERE TRUE;


SELECT SUM(`t0`.`Quantity`) AS `res0`
FROM `InvoiceLine` AS `t0`;

-- Output: With a few simple lines, we've double our sales figure to Just 4480 products sold!

Amazing! A few simple lines, and we've doubled our sales -- beam is awesome!