Pre-General Availability: 2024-07-17

6.3 Join Tables, Update Records, and Insert Values

Join sakila.address, sakila.city, sakila.country, and sakila.customer tables

SELECT cus.last_name AS 'Last Name', ad.address, ci.city AS 'City', ctr.country 
FROM sakila.customer cus
JOIN sakila.address ad ON (cus.address_id = ad.address_id)
JOIN sakila.city ci ON (ad.city_id = ci.city_id)
JOIN sakila.country ctr ON (ci.country_id = ctr.country_id)
WHERE ctr.country = 'canada' OR ctr.country = 'algeria'
ORDER BY ctr.country ASC;

The output is:

+-----------+-------------------------+---------------+---------+
| Last Name | address                 | City          | country |
+-----------+-------------------------+---------------+---------+
| CHEATHAM  | 1924 Shimonoseki Drive  | Batna         | Algeria |
| GRAY      | 1031 Daugavpils Parkway | Bchar         | Algeria |
| CARROLL   | 757 Rustenburg Avenue   | Skikda        | Algeria |
| BOURQUE   | 1153 Allende Way        | Gatineau      | Canada  |
| POWER     | 1844 Usak Avenue        | Halifax       | Canada  |
| CARPENTER | 891 Novi Sad Manor      | Oshawa        | Canada  |
| IRBY      | 432 Garden Grove Street | Richmond Hill | Canada  |
| QUIGLEY   | 983 Santa F Way         | Vancouver     | Canada  |
+-----------+-------------------------+---------------+---------+
OK, 8 records retrieved in 3.092ms

Update an individual record in sakila.actor

-- error in the following entry
-- +----------+-------------+--------------+---------------------+
-- | actor_id | first_name  | last_name    | last_update         |
-- |        8 | MATTHEW     | JOHANSSON    | 2006-02-15 04:34:33 |

UPDATE sakila.actor
SET first_name = 'John', last_name = 'Matthewson'
WHERE actor_id = 8;

Insert values to sakila.actor

INSERT INTO sakila.actor (first_name, last_name)
  VALUES 
  ('Jimmy', 'Smith'),
  ('Bob', 'Roberts');