SQL UPDATE

Summary: in this tutorial, you will learn how to use the SQL UPDATE statement to modify data of the existing rows a table.

Introduction to the SQL UPDATE statement

To change existing data in a table, you use the UPDATE statement. The following shows the syntax of the UPDATE statement:

UPDATE table_name
SET column1 = value1,
 column2 = value2
WHERE
	condition;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, indicate the table that you want to update in the UPDATE clause.
• Second, specify the columns that you want to modify in the SET clause. The columns that are not listed in the SET clause will retain their original values.
• Third, specify which rows to update in the WHERE clause.

The UPDATE statement affects one or more rows in a table based on the condition in the WHERE clause.  For example, if the WHERE clause contains a primary key expression, the UPDATE statement changes one row only.

However, any row that causes the condition in the WHERE to evaluate to true will be modified. Because the WHERE clause is optional, therefore, if you omit it, the all the rows in the table will be affected.

SQL UPDATE statement examples

We will use the employees and dependents table to demonstrate the UPDATE statement.

SQL UPDATE one row example

Suppose the employee id 192 Sarah Bell changed her last name from Bell to Lopez and you need to update her record in the  employees table.

To update Sarah’s last name from  Bell to Lopez, you use the following UPDATE statement:

UPDATE employees 
SET 
    last_name = 'Lopez'
WHERE
    employee_id = 192;
Code language: SQL (Structured Query Language) (sql)

Try It

The database system updated value in the last_name column and the row with  employee_id 192.

You can verify it by using the following SELECT statement.

SELECT
	employee_id,
	first_name,
	last_name
FROM
	employees
WHERE
	employee_id = 192;
Code language: SQL (Structured Query Language) (sql)

Try It

SQL UPDATE multiple rows example

Now, Nancy wants to change all her children’s last names from Bell to Lopez. In this case, you need to update all Nancy’s dependents in the dependents table.

Before updating the data, let’s check the dependents of Nancy.

SELECT
	*
FROM
	dependents
WHERE
	employee_id = 192;
Code language: SQL (Structured Query Language) (sql)

Try It

To update the last names of Nancy’s dependents, you use the following UPDATE statement.

UPDATE dependents 
SET 
    last_name = 'Lopez'
WHERE
    employee_id = 192;
Code language: SQL (Structured Query Language) (sql)

Try It

SQL UPDATE with subquery example

Sometimes when employees change their last names, you update the  employees table only without updating the dependents table.

To make sure that the last names of children are always matched with the last name of parents in the  employees table, you use the following statement:

UPDATE dependents
SET last_name = (
	SELECT
		last_name
	FROM
		employees
	WHERE
		employee_id = dependents.employee_id
);
Code language: SQL (Structured Query Language) (sql)

Try It

Because the WHERE clause is omitted, the UPDATE statement updated all rows in the dependents table.

In the SET clause, instead of using the literal values, we used a subquery to get the corresponding last name value from the  employees table.

In this tutorial, we have shown you how to use the SQL UPDATE statement to modify existing data in a table.

 

SQL LIKE

Summary: in this tutorial, you will learn how to use the SQL LIKE operator to test whether a value matches a pattern.

Introduction to SQL LIKE operator

The LIKE operator is one of the SQL logical operators. The LIKE operator returns true if a value matches a pattern or false otherwise.

The syntax of the LIKE operator is as follows:

expression LIKE pattern
Code language: SQL (Structured Query Language) (sql)

In this syntax, the LIKE operator tests whether an expression matches the pattern. The SQL standard provides you with two wildcard characters to make a pattern:

•  % percent wildcard matches zero, one, or more characters
•  _ underscore wildcard matches a single character.

The following show an example of using the % and _ wildcard characters:

Expression	Meaning
LIKE 'Kim%'	match a string that starts with Kim
LIKE '%er'	match a string that ends with er
LIKE '%ch%'	match a string that contains ch
LIKE 'Le_'	match a string that starts with Le and is followed by one character e.g., Les, Len…
LIKE '_uy'	match a string that ends with uy and is preceded by one character e.g., guy
LIKE '%are_'	match a string that contains are and ends with one character
LIKE '_are%'	match a string that contains are, starts with one character, and ends with any number of characters

Note that besides the % and _ wildcards, some database systems may have other wildcard characters that are specific to those databases.

NOT LIKE

To negate the LIKE operator, you use the NOT operator:

expression NOT LIKE pattern

The NOT LIKE operator returns true if the expression doesn’t match the pattern or false otherwise.

Escape character

To match a string that contains a wildcard for example 10%, you need to instruct the LIKE operator to treat the % in 10% as a regular character.

To do that, you need to explicitly specify an escape character after the ESCAPE clause:

expression LIKE pattern ESCAPE escape_character
Code language: SQL (Structured Query Language) (sql)

For example:

value LIKE '%10!%%' ESCAPE '!'
Code language: JavaScript (javascript)

In this example, the ! is an escape character. It instructs the LIKE operator to treat the % in the 10% as a regular character.

In practice, you often use the LIKE operator in  WHERE clause of the SELECT, UPDATE, and DELETE statements.

SQL LIKE operator examples

We’ll use the employees table in the sample database for the demonstration.

The following example uses the LIKE operator to find all employees whose first names start with Da :

SELECT
	employee_id,
	first_name,
	last_name
FROM
	employees
WHERE
	first_name LIKE 'Da%';
Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         105 | David      | Austin    |
|         109 | Daniel     | Faviet    |
+-------------+------------+-----------+
Code language: plaintext (plaintext)

The following example use the LIKE operator to find all employees whose first names end with er:

SELECT
	employee_id,
	first_name,
	last_name
FROM
	employees
WHERE
	first_name LIKE '%er';
Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         103 | Alexander  | Hunold    |
|         115 | Alexander  | Khoo      |
|         200 | Jennifer   | Whalen    |
+-------------+------------+-----------+
Code language: plaintext (plaintext)

The following example uses the LIKE operator to find employees whose last names contain the word an:

SELECT
	employee_id,
	first_name,
	last_name
FROM
	employees
WHERE
	last_name LIKE '%an%';
Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+-------------+-----------+
| employee_id | first_name  | last_name |
+-------------+-------------+-----------+
|         102 | Lex         | De Haan   |
|         112 | Jose Manuel | Urman     |
|         123 | Shanta      | Vollman   |
|         178 | Kimberely   | Grant     |
+-------------+-------------+-----------+
Code language: plaintext (plaintext)

The following statement retrieves employees whose first names start with Jo and are followed by at most 2 characters:

SELECT
	employee_id,
	first_name,
	last_name
FROM
	employees
WHERE
	first_name LIKE 'Jo__';
Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         110 | John       | Chen      |
|         145 | John       | Russell   |
+-------------+------------+-----------+
Code language: plaintext (plaintext)

The following statement uses the LIKE operator with the % and _ wildcard to find employees whose first names start with any number of characters and are followed by at most one character:

SELECT
	employee_id,
	first_name,
	last_name
FROM
	employees
WHERE
	first_name LIKE '%are_';
Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+------------+------------+
| employee_id | first_name | last_name  |
+-------------+------------+------------+
|         119 | Karen      | Colmenares |
|         146 | Karen      | Partners   |
+-------------+------------+------------+

SQL NOT LIKE operator example

The following example uses the NOT LIKE operator to find all employees whose first names start with the letter S but not start with Sh:

SELECT
	employee_id,
	first_name,
	last_name
FROM
	employees
WHERE
	first_name LIKE 'S%'
AND first_name NOT LIKE 'Sh%'
ORDER BY
	first_name;
Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         192 | Sarah      | Bell      |
|         117 | Sigal      | Tobias    |
|         100 | Steven     | King      |
|         203 | Susan      | Mavris    |
+-------------+------------+-----------+
Code language: plaintext (plaintext)

Summary

• The LIKE operator returns true if a value matches a pattern or false otherwse.
• Use the NOT operator to negate the LIK opeator.
• Use the % wildcard to match one or more characters
• Use the _ wildcard to match a single character.

 

SQL Alias

Summary: in this tutorial, you will learn about SQL aliases including table and column aliases to make your queries shorter and more understandable.

SQL alias allows you to assign a table or a column a temporary name during the execution of a query. SQL has two types of aliases: table and column aliases.

SQL column aliases

When designing database tables, you may use abbreviations for the column names to keep them short. For example:

• The so_no stands for sales order number.
• The qty stands for quantity.

Or you may have to work with legacy systems that aggressively use abbreviations for naming columns and tables.

For example, SAP ERP uses abbreviations (in German) to name all the columns and tables such as VBELN for naming sales document number column.

When you use SQL to query data from these tables, the output is not obvious. To fix it, you can use column aliases that give columns temporary names during the execution of the query.

The following shows the syntax of using column aliases:

column_name AS alias_name
Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the column alias after the AS keyword followed by the column name. The AS keyword is optional. So you can omit it like this:

column_name alias_name
Code language: SQL (Structured Query Language) (sql)

If the alias contains spaces, you need to put it inside single quotes (or double quotes) like this:

column_name AS 'Alias Name'
Code language: SQL (Structured Query Language) (sql)

The following example shows how to use the column aliases:

SELECT
	inv_no AS invoice_no,
	amount,
	due_date AS 'Due date',
	cust_no 'Customer No'
FROM
	invoices;
Code language: SQL (Structured Query Language) (sql)

This query has multiple column aliases:

• The invoice_no is the column alias of the inv_no column.
• The 'Due date' is the column alias of the due_date column. Because the alias contains space, you have to place it inside single quote (‘) or double quotes (“) .
• The 'Customer no' is the alias of the cust_no column. Note that it doesn’t has the AS keyword.

Aliases for expressions

If a query contains expressions, you can assign column aliases to the expressions. For example:

SELECT 
    first_name, 
    last_name, 
    salary * 1.1 AS new_salary
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

In this example, the database will use the column aliases as the heading of the expressions in the result set.

Common mistakes of column aliases

Since you assign aliases to columns in the SELECT clause, you can only reference the aliases in the clauses that are evaluated after the SELECT clause.

The following query will result in an error:

SELECT 
    first_name, 
    last_name, 
    salary * 1.1 AS new_salary
FROM
    employees
WHERE new_salary > 5000
Code language: SQL (Structured Query Language) (sql)

Error:

Unknown column 'new_salary' in 'where clause'
Code language: JavaScript (javascript)

Why?

In this SELECT statement, the database evaluates the clauses in the following order:

FROM > WHERE > SELECT
Code language: SQL (Structured Query Language) (sql)

The database evaluates the WHERE clause before the SELECT clause. Therefore, at the time it evaluates the WHERE clause, the database doesn’t have the information of the new_salary column alias. So it issued an error.

However, the following query works correctly:

SELECT 
    first_name, 
    last_name, 
    salary * 1.1 AS new_salary
FROM
    employees
ORDER BY new_salary;
Code language: SQL (Structured Query Language) (sql)

In this example, the database evaluates the clauses of the query in the following order:

FROM > SELECT > ORDER BY
Code language: SQL (Structured Query Language) (sql)

The database evaluates the SELECT clause before the ORDER BY clause. Therefore, at the time of evaluating the ORDER BY clause, the database has the information of the new_salary alias, which is the alias of the expression salary * 1.1. Hence, it works as expected.

SQL table aliases

Like columns, you can assign aliases to tables. And these aliases are called table aliases.

To assign an alias to a table, you use the following syntax:

table_name AS table_alias
Code language: PHP (php)

In this syntax, the AS keyword is also optional. So you can omit it like the following:

table_name table_alias
Code language: SQL (Structured Query Language) (sql)

Notice that assigning an alias to a table does not rename the table permanently. It just gives the table another name temporarily during the execution of a query.

So why do you need table aliases?

When specifying the column names in the SELECT clause, you can use the following syntax:

table_name.column_name
Code language: CSS (css)

In this syntax, the column has a fully qualified name that includes both table and column names. For example:

SELECT 
    employees.first_name, 
    employees.last_name
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

In this example, the query selects the data from the first_name and last_name columns of the employees table. Unlike a typical query, we specify the fully qualified names for the first_name and last_name columns.

In the SELECT clause, instead of using the employees table name, you can use the table alias of the employees table. For example:

SELECT 
    e.first_name, 
    e.last_name
FROM
    employees AS e;
Code language: SQL (Structured Query Language) (sql)

In this example, we assign e table alias to the employees in the FROM clause and reference it in the SELECT clause.

But why do you need to use the fully qualified names for columns?

See the following employees and departments tables from the sample database:

Both employees and departments tables have the columns with the same name: department_id.

When querying data from both tables, you need to specify the exact table that the department_id column belongs to. Otherwise, you’ll get an error because the database doesn’t know which table it needs to go to select data.

If you want to select data from the employees table, you can reference it in the SELECT clause as follows:

employees.department_id
Code language: SQL (Structured Query Language) (sql)

The same is applied to the department_id of the departments table:

departments.department_id
Code language: SQL (Structured Query Language) (sql)

If e and d are table aliases of the employees and departments tables, you can reference the department_id column in each table using the table aliases as follows:

e.department_id
d.department_id
Code language: SQL (Structured Query Language) (sql)

In the next tutorial, you’ll learn how to use the join clauses to select data from both tables and apply the table aliases. Also, you’ll learn how to reference the same table in a single query twice using the self-join technique. In this case, you need to use the table aliases.

Summary

• SQL has two type of aliases: column and table aliases.
• Reference the column aliases in the clauses that are evaluated after the SELECT clause.
• Use table aliases to qualify the column names.