SQL Update

The SQL UPDATE statement is used to modify the existing records in a table. So using the WHERE clause we can filter out those data which we want to update and set new values for those fields.

SQL UPDATE statement

For updating a single record we can use a unique identifier such as “id” (i.e.: Primary Key ) in the where clause.
where else for updating the same value for multiple records we can use some other common fields.
for example set status to active for all customers from specific countries.

    UPDATE table_name
    SET column_1 = value_1, column_2 = value_2
    WHERE condition;
  

Query

Consider we have a customer table in which the status column is a boolean field that defines is customer active or not.

Update Single Record
The following SQL statement updates the third customer (id = 3 & name = "Yaya Vanakova" ) with a new name and status as true.

  UPDATE customers
  SET name = 'Alfred Schmidt', status = TRUE
  WHERE id = 3 && name = 'Yaya Vanakova';

  -- 1 record updated.
  -- +-------------------------------------------+
  -- | id  | name            | country  | status |
  -- |-------------------------------------------|
  -- | 1   | Steve           | USA      | TRUE   |
  -- | 2   | John            | USA      | TRUE   |
  -- | 3   | Alfred Schmidt  | Japan    | TRUE   |
  -- +-------------------------------------------+

Update Multiple Record
The following SQL statement updates the status to FALSE if country is USA.

  UPDATE customers
  SET status = FALSE
  WHERE country = 'USA';

  -- 2 record updated.
  -- +---------------------------------------------+
  -- | id  | name            | country  | status   |
  -- |---------------------------------------------|
  -- | 1   | Steve           | USA      | FALSE    |
  -- | 2   | John            | USA      | FALSE    |
  -- | 3   | Alfred Schmidt  | Japan    | TRUE     |
  -- +---------------------------------------------+

    UPDATE customers
    SET status = FALSE , country = 'USA'
    -- WHERE name = 'Steve';  // By mistake comment-out or forget the WHERE clause will affect all the data

    -- 278 record updated.
    -- +---------------------------------------------+
    -- | id  | name            | country  | status   |
    -- |---------------------------------------------|
    -- | 1   | Steve           | USA      | FALSE    |
    -- | 2   | John            | USA      | FALSE    |
    -- | 3   | Alfred Schmidt  | USA      | FALSE    |
    -- | .   | .               | .        | .        |
    -- | .   | .               | .        | .        |
    -- | .   | .               | .        | .        |
    -- | 278 | Will Smith      | USA      | FALSE    |
    -- +---------------------------------------------+
  

.
SQL Complete Guide