UPDATE

The UPDATE statement is used to update the data that already exists in a table in a SQL database. This is practically very useful because often times we will need to update information in tables to reflect the new and correct information. The UPDATE statement is a way to update information but at individual rows or by multiple updates.

Let's begin by querying some data to determine a suitable condition to update rows

Below is a query that returns the first 5 rows of data.

select * from products limit 5;

OUTPUT product_id | product_name | category | price | cost | stock_quantity | supplier_id | launch_date | weight_kg | is_discontinued | rating ------------+--------------------+-------------+---------+---------+----------------+-------------+-------------+-----------+-----------------+-------- 1 | iPhone 14 Pro | Smartphones | 999.99 | 650.00 | 45 | 1 | 2022-09-16 | 0.21 | f | 4.7 2 | Samsung Galaxy S23 | Smartphones | 799.99 | 520.00 | 38 | 2 | 2023-02-01 | 0.17 | f | 4.5 3 | MacBook Pro 16" | Laptops | 2499.99 | 1800.00 | 12 | 1 | 2021-10-26 | 2.15 | f | 4.8 4 | Dell XPS 13 | Laptops | 1299.99 | 900.00 | 23 | 3 | 2022-01-15 | 1.27 | f | 5 | iPad Air | Tablets | 599.99 | 400.00 | 67 | 1 | 2022-03-18 | 0.46 | f | 4.4 (5 rows)

Now let's update the price of iPhone 14 Pro from 999 to 800 and stock_quantity from 45 to 34. We can do this by specifying some matching conditions. For example, if a row contains product_name='iPhone 14 Pro', set the price to 800

UPDATE products SET (price, stock_quantity) = (800, 34) 
WHERE product_name='iPhone 14 Pro';

Running this code will return the following code

$ UPDATE products SET (price, stock_quantity) = (800, 34)
WHERE product_name='iPhone 14 Pro';
UPDATE 1

Now let's check the actual data by running the same query. Notice that in some instances, the row my be appended to the last raw. Here we use order and limit to preserve the output and compare it.

select * from products order by 1 limit 5;

OUTPUT product_id | product_name | category | price | cost | stock_quantity | supplier_id | launch_date | weight_kg | is_discontinued | rating ------------+--------------------+-------------+---------+---------+----------------+-------------+-------------+-----------+-----------------+-------- 1 | iPhone 14 Pro | Smartphones | 800.00 | 650.00 | 34 | 1 | 2022-09-16 | 0.21 | f | 4.7 2 | Samsung Galaxy S23 | Smartphones | 799.99 | 520.00 | 38 | 2 | 2023-02-01 | 0.17 | f | 4.5 3 | MacBook Pro 16" | Laptops | 2499.99 | 1800.00 | 12 | 1 | 2021-10-26 | 2.15 | f | 4.8 4 | Dell XPS 13 | Laptops | 1299.99 | 900.00 | 23 | 3 | 2022-01-15 | 1.27 | f | 5 | iPad Air | Tablets | 599.99 | 400.00 | 67 | 1 | 2022-03-18 | 0.46 | f | 4.4 (5 rows)

As suspected, we get the updates price and quantity values

UPDATE without Conditions

Naturally, in the first example, the update is applied with specific filtering conditions. However, we can simply update a column without any specific condition. For example, we can set rating=3.5 for all observations. Using the following command.

UPDATE products SET rating=3.5;

OUTPUT$ UPDATE products SET rating=3.5; UPDATE 52

select * from products order by 1 limit 5;

OUTPUT product_id | product_name | category | price | cost | stock_quantity | supplier_id | launch_date | weight_kg | is_discontinued | rating ------------+--------------------+-------------+---------+---------+----------------+-------------+-------------+-----------+-----------------+-------- 1 | iPhone 14 Pro | Smartphones | 800.00 | 650.00 | 34 | 1 | 2022-09-16 | 0.21 | f | 3.5 2 | Samsung Galaxy S23 | Smartphones | 799.99 | 520.00 | 38 | 2 | 2023-02-01 | 0.17 | f | 3.5 3 | MacBook Pro 16" | Laptops | 2499.99 | 1800.00 | 12 | 1 | 2021-10-26 | 2.15 | f | 3.5 4 | Dell XPS 13 | Laptops | 1299.99 | 900.00 | 23 | 3 | 2022-01-15 | 1.27 | f | 3.5 5 | iPad Air | Tablets | 599.99 | 400.00 | 67 | 1 | 2022-03-18 | 0.46 | f | 3.5 (5 rows)

This concludes our discussion on UPDATE. Next we move to CAST