Learning MySQL By Example

1. How to Retrieve Data From a Single Table

1.1. The Five Clauses of the SELECT Statement

1.2. Column Specifications

1.3. LIKE and REGEXP Operators

1.4. Arithmetic Operators

1.5. Column Aliases

1.6. Comparison Operators

1.7. IS NULL, BETWEEN, IN Operators

1.8. AND, OR, NOT Logical Operators

1.9. DISTINCT Clause

2. How to Retrieve Data from Multiple Tables

2.1. The JOIN Clause

2.2. Joining More Than Two Tables

2.3. The OUTER JOIN Clause

2.4. How to Code a UNION

3. Using Functions

3.1. Date Functions

3.2. Numeric Functions

3.3. String Functions

4. How to Insert, Update, Delete Data in Tables

4.1. The INSERT Clause With a Column List

4.2. The INSERT Clause Without a Column List

4.4. The UPDATE Clause With a Column List

4.4. The DELETE Clause

5. Summary Queries and Aggregate Functions

5.1. Aggregate Functions

5.2. Grouping Data

5.3. Simple GROUP BY Query

5.4. Improving the GROUP BY Query

5.5. Using the HAVING Clause

5.5. Using the HAVING and WHERE Clauses Together

5.6. COUNT(column_name) and COUNT(*)

5.7. Using the DISTINCT Statement

6. Working With Subqueries

6.1. The Subquery In a SELECT Statement

6.3. Create a Duplicate Table From An Existing Table

6.4. The Subquery In a Delete Statement

7.1. SQL View Explained

7.2. Benefits of Using Views

7.3. Views That Allow UPDATE Statements

8.1. SQL Indexes Explained

8.2. Clustered vs. Non-clustered Indexes

8.3. Create an Index in Workbench Using an ERD

8.4. How to Manually Add an Index to an Existing Table

6.2

The Subquery in an UPDATE statement

The Subquery in an UPDATE statement

Subqueries may be used in an UPDATE statement
Since it is possible to change many values at once with a subquery, take special care before running an UPDATE statement with a subquery. You might make a copy of the table and data you are trying to change to test with before running your statement on live data.
It is also possible to run your UPDATE statement inside of a transaction block that allows you to ROLLBACK or undo a statement. We will address the topic of ROLLBACK in a future lesson.

Code Sample:

1 UPDATE country 2 SET GNPOld = 0.00 3 WHERE Code IN 4 (SELECT CountryCode FROM countrylanguage WHERE population = 0)

Results:

UPDATE country

Update the country table

SET GNPOld = 0.00

Set the value of the GNPOld table = 0.00.
No quotes are required because the GNPOld column is a decimal datatype

WHERE Code IN

Update only the rows where the Code column value is in the results list returned in the subquery show below.

(SELECT CountryCode FROM countrylanguage WHERE population = 0)

Return a list of values from the CountryCode column from the countrylanguage table that has a population equal to zero.
If these values match a code in the country table, the row is updated.

This content is provided to you freely by EdTech Books.

Access it online or download it at https://edtechbooks.org/learning_mysql/the_subquery_in_an_u.