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") = (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 (14) = ("CustomerId");
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")=(?);
-- 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"
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 (which can be combined monoidally), 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_ @Int32 countAll_) $
filter_ (\c -> addressCountry (customerAddress c) ==. val_ (Just "Canada")) $
all_ (customer chinookDb)
Just usaCount <-
runSelectReturningOne $ select $
aggregate_ (\_ -> as_ @Int32 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_ @Int32 countAll_) $
filter_ (\c -> addressCountry (customerAddress c) ==. val_ (Just "Canada")) $
all_ (customer chinookDb)
Just usaCount' <-
runSelectReturningOne $ select $
aggregate_ (\_ -> as_ @Int32 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 ("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 ((?) 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.
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";
-- 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";
-- 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!
Amazing! A few simple lines, and we've doubled our sales -- beam is awesome!