Databricks SQL Tutorial: Update Delta Rows with Precise Predicates

This tutorial demonstrates how to update rows in a Delta table using precise predicates in Databricks SQL. We’ll progress through several scripts, each focusing on a different aspect of this process.

Script 1: Initial Data and Basic Update

Let’s start with a simple scenario where we want to increase the price of all products with a specific ID.

-- Create a Delta table named 'products'
CREATE TABLE products (
    product_id INT,
    product_name STRING,
    price DECIMAL(10, 2)
)
;

-- Insert some initial data
INSERT INTO products (product_id, product_name, price) VALUES
  (1, 'Laptop', 1200.00),
  (2, 'Mouse', 25.00),
  (3, 'Keyboard', 75.00);

-- Update the price of product_id = 1 to 1300.00
UPDATE products
SET price = 1300.00
WHERE product_id = 1;

-- Verify the update
SELECT  FROM products;

This script first creates a Delta table named ‘products’ with three columns: product_id, product_name, and price. Then, it inserts some initial data. Finally, it updates the price of the product with product_id = 1 to 1300.00. The final SELECT statement verifies that the update was successful.

Output:

1

Script 2: Using a More Complex Predicate

Now, let’s introduce a more complex predicate to update rows based on multiple conditions. We’ll update the price of products where the product name contains ‘Laptop’ and the price is less than 1200.00.

-- Create a Delta table named 'products' (if it doesn't exist)
CREATE TABLE IF NOT EXISTS products (
    product_id INT,
    product_name STRING,
    price DECIMAL(10, 2)
)
;

-- Insert some initial data
INSERT INTO products (product_id, product_name, price) VALUES
  (1, 'Laptop', 1200.00),
  (2, 'Mouse', 25.00),
  (3, 'Keyboard', 75.00),
  (4, 'Gaming Laptop', 1500.00);

-- Update the price of products containing 'Laptop' and priced below 1200.00
UPDATE products
SET price = 1100.00
WHERE product_name LIKE '%Laptop%' AND price < 1200.00;

-- Verify the update
SELECT  FROM products;

This script builds upon the previous one. It updates the price of products where the `product_name` column contains the string ‘Laptop’ (using `LIKE ‘%Laptop%’` for partial matching) and the `price` is less than 1200.00. The `IF NOT EXISTS` clause ensures the table is created if it doesn’t already exist. The final SELECT statement validates the update.

Output:

1

Script 3: Updating Based on a Range

This script demonstrates updating based on a range of values. We will update the price of products whose price is between 50 and 75.

-- Create a Delta table named 'products' (if it doesn't exist)
CREATE TABLE IF NOT EXISTS products (
    product_id INT,
    product_name STRING,
    price DECIMAL(10, 2)
)
;

-- Insert some initial data
INSERT INTO products (product_id, product_name, price) VALUES
  (1, 'Laptop', 1200.00),
  (2, 'Mouse', 25.00),
  (3, 'Keyboard', 75.00),
  (4, 'Gaming Laptop', 1500.00),
  (5, 'Small Keyboard', 60.00);

-- Update the price of products between 50 and 75 (inclusive)
UPDATE products
SET price = 65.00
WHERE price BETWEEN 50.00 AND 75.00;

-- Verify the update
SELECT  FROM products;

This script updates the price of products where the `price` falls within the range of 50.00 and 75.00 (inclusive). The `BETWEEN` operator simplifies this predicate. The final SELECT statement confirms the changes.

Output:

1