# Update

To update (modify) data in a table the `UPDATE` statement is used. `UPDATE` can be used in two ways:

1. To update specific rows in a table
2. To update all rows in a table

The basic format of an `UPDATE` statement is made up of three parts:

1. The table to be updated
2. The column names and their new values
3. The filter condition that determines which rows should be updated

```php
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
```

## Update Single Column

Assume customer 1000000005 has no e-mail address on file and now has an address, and so that record needs updating. The following statement performs this update:

```php
UPDATE Customers
SET cust_email = "kim@thetoystore.com"
WHERE cust_id = "1000000005";
```

If you don't use `WHERE` clause, all the rows will be updated.

## Update Multiple Columns

```php
UPDATE Customers
SET cust_contact = 'Sam Roberts', cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';
```

## Update Under Different Conditions

```php
UPDATE salary
SET sex = (CASE sex WHEN 'f' THEN 'm' WHEN 'm' THEN 'f' END);
```

## Cross-table Update: UPDATE JOIN

If we need information in another table when we update values, we need to JOIN the tables.

```php
UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition
```

In the following code, we want to update salary in employees table and we need percentage in merits table.

```php
UPDATE employees
    INNER JOIN merits 
    ON employees.performance = merits.performance 
SET 
    salary = salary + salary * percentage;
```

If join three tables:

```php
UPDATE TABLE_A a 
    JOIN TABLE_B b ON a.join_col = b.join_col AND a.column_a = b.column_b 
    JOIN TABLE_C c ON [condition]
SET a.column_c = a.column_c + 1
```
