UPDATE
The
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;
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
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;
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
UPDATE products SET rating=3.5;
$ UPDATE products SET rating=3.5; UPDATE 52
select * from products order by 1 limit 5;
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