DML: Update and Delete

name: portada
layout: true
class: portada-slide, middle, right
---
# DML: Update and Delete
## Databases

.footnote[Joan Puigcerver Ibáñez ([j.puigcerveribanez@edu.gva.es](mailto:j.puigcerveribanez@edu.gva.es))]

---
layout: true
class: regular-slide
.right[.logo[![logoMislata](/itb/images/logo_mislata.png)]]

---
# Update
Data in the database can be modified using the `UPDATE` statement.

Joins and Subqueries __can be used__ in `UPDATE` statements.

.center[[MariaDB UPDATE](https://mariadb.com/kb/en/update/)]

```
UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col1={expr1|DEFAULT} [, col2={expr2|DEFAULT}] ...
    [WHERE where_condition]
```

- _Change the length of the films from minutes to seconds_

```
UPDATE films SET length = length * 60;
```

- _Change the length of the film "The Lord of the Rings: The Return of the King" to 201 minutes_

```
UPDATE films SET length = 201 
WHERE title = "The Lord of the Rings: The Return of the King";
```

---
# Update multiple tables

- _Change the DNI from the student "0000000A" to "0000001B"_

```
UPDATE student s
INNER JOIN enrolled e
    on e.studentDNI = s.DNI
SET s.DNI = "00000001B", e.studentDNI = "00000001B"
WHERE s.DNI = "00000000A";
```

---
# Delete
Data in the database can be deleted using the `DELETE` statement.

Joins and Subqueries __can be used__ in `UPDATE` statements.

.center[[MariaDB DELETE](https://mariadb.com/kb/en/delete/)]

```
DELETE tbl_name [, tbl_name] ...
    FROM table_references
    [WHERE where_condition]
```

- _Delete all films that are shorter than 60 minutes_

```
DELETE FROM film
WHERE length < 60;
```

---
# Delete from multiple tables

- _Delete the student with DNI "0000000A" and all its grades_

```
DELETE s, e
FROM student s
INNER JOIN enrolled e
    on e.studentDNI = s.DNI
WHERE s.DNI = "00000000A";
```