Transform Data with DML in BigQuery

Learning Data Manipulation Language (DML) in SQL is crucial for anyone working with databases, as it provides the ability to interact with and modify data effectively. DML commands such as INSERT, UPDATE, DELETE, and MERGE allow users to add new records, update existing ones, or remove unwanted data from tables.

Understanding DML is essential for maintaining data integrity and ensuring the accuracy and consistency of information within the database. Furthermore, DML proficiency is fundamental for data transformation and data cleansing tasks, enabling users to manipulate data to meet specific requirements or standards.

Create a table with data manipulation language
Insert data into a table
Update data in rows and columns
Merge tables
Delete data from table

CREATE table

The CREATE OR REPLACE TABLE statement, though typically associated with DDL, merits inclusion here due to its close association with DML operations.

It guides the action: If the table tutorial.personnel does not exist, then create a new one; If the table exists, then update it. Then we need to declare the table’s Schema, which contains column name and data type.

CREATE OR REPLACE TABLE tutorial.personnel (
  name STRING,
  age INT64,
  salary FLOAT64,
  is_Male BOOLEAN
);

Field name	Type	Mode
name	STRING	NULLABLE
age	INTEGER	NULLABLE
salary	FLOAT	NULLABLE
is_Male	BOOLEAN	NULLABLE

INSERT data

INSERT INTO tutorial.personnel(name, age, salary, is_male) VALUES
  ('John', 30, 50000.00, True),
  ('Alice', 25, 60000.00,False),
  ('Bob', 35, NULL,True),
  ('Emma', NULL, 70000.00,True),
  (NULL, 28, 55000.00,True),
  ('Michael', 40, NULL,False),
  ('Sara', 33, 65000.00,False),
  (NULL, NULL, NULL,NULL),
  ('David', 45, 75000.00,True),
  ('Sophia', 27, 58000.00,False);

#This statement added 10 rows to personnel.

Row	name	age	salary	is_Male
1	null	null	null	null
2	Sara	33	65000.0	false
3	Michael	40	null	false
4	Sophia	27	58000.0	false
5	Alice	25	60000.0	false
6	John	30	50000.0	true
7	null	28	55000.0	true
8	Bob	35	null	true
9	David	45	75000.0	true
10	Emma	null	70000.0	true

You also can combine the WITH clause with INSERT SELECT. For example

# create a new table called fruit
CREATE OR REPLACE TABLE tutorial.personnel (
  fruit_name STRING,
  quantity INT64
);

# insert data with WITH clause
INSERT tutorial.fruit (fruit_name, quantity)
WITH fruit_shop AS (
  SELECT ARRAY<STRUCT<fruit_name string, quantity integer>>
      [('#1 Apple', 500),
       ('#2 Ananas', 300),
       ('#3 Grape', 1000),
       ('#4 Banana', 800)
       ] col
)
SELECT fruit_name, quantity FROM fruit_shop, UNNEST(fruit_shop.col)

Row	fruit_name	quantity
1	#3 Grape	1000
2	#1 Apple	500
3	#4 Banana	800
4	#2 Ananas	300

UPDATE table

We can use UPDATE to update data in a row, in column or the column itself based on specific conditions. That’s why UPDATE always combines with SET and WHERE.

Update data in column

Let’s review the personnel table. In the 3rd row, Michael is identified as a man, yet the ‘is_male’ column is marked as false. Conversely, in the 10th row, Emma is recognized as a woman, yet the ‘is_male’ column is set to true. We must correct these discrepancies.

UPDATE instruction-415216.tutorial.personnel    
SET is_male = True
WHERE name = 'Michael';

UPDATE instruction-415216.tutorial.personnel
SET is_male = FALSE
WHERE name = 'Emma';

# verify the result
SELECT name, is_male
FROM `instruction-415216.tutorial.personnel`
WHERE name IN ('Michael','Emma');

Row	name	is_male
1	Emma	false
2	Michael	true

Update data in row

We noticed that the first row has no values. We can update information in this row.

UPDATE `instruction-415216.tutorial.personnel`
SET name = 'Ivy', age = 25, salary = 60000, is_Male = False   
WHERE name is NULL and age is NULL;      # specify the conditions

# check the result 
SELECT * FROM `instruction-415216.tutorial.personnel`
WHERE name = 'Ivy'

Row	name	age	salary	is_Male
1	Ivy	25	60000.0	false

MERGE table

A MERGE statement is a DML statement that can combine INSERT, UPDATE, and DELETE operations into a single statement and perform the operations atomically.

MERGE tutorial.personnel AS target
USING (
  SELECT 
    name,
    age,
    IFNULL(salary, 10000.00) AS salary,        # Use default salary of 10000 if salary is NULL
    is_male
  FROM tutorial.personnel
) AS source
ON target.name = source.name AND target.age IS NOT NULL
WHEN MATCHED THEN
  UPDATE SET
    salary = source.salary;

Row	name	age	salary	is_Male
6	Michael	40	10000.0	true
7	Bob	35	10000.0	true

DELETE data from table

You can delete rows based on specific conditions with DELETE clause. You need to declare the condition, too.
1. For example, I want to delete rows that salary is under 20000.

DELETE `instruction-415216.tutorial.personnel`
WHERE salary < 20000

Row	name	age	salary	is_Male
1	Emma	null	70000.0	false
2	Alice	25	60000.0	false
3	Ivy	25	60000.0	false
4	Sophia	27	58000.0	false
5	Sara	33	65000.0	false
6	null	28	55000.0	true
7	John	30	50000.0	true
8	David	45	75000.0	true

2. What if I want to remove all rows containing missing value( null)?

DELETE `instruction-415216.tutorial.personnel`
WHERE name IS NULL OR salary IS NULL OR age IS NULL

Row	name	age	salary	is_Male
1	Ivy	25	60000.0	false
2	Alice	25	60000.0	false
3	Sophia	27	58000.0	false
4	Sara	33	65000.0	false
5	John	30	50000.0	true
6	David	45	75000.0	true

3. Remove all rows

If you want to remove all rows in a table, you can use TRUNCATE TABLE .

The TRUNCATE TABLE statement removes all rows from a table but leaves the table metadata intact, including the table schema, description, and labels.

TRUNCATE TABLE  `instruction-415216.tutorial.personnel`

If you check the preview, it shows that there is no data to display.
If you check the Schema, it still remains.

Share on Social Media