MariaDB UPDATE Statement


Objectives

  • Understanding of MariaDB UPDATE Statement

  • MariaDB UPDATE Single Record

  • MariaDB UPDATE Multiple Records

  • MariaDB UPDATE Multiple Records without Condition


Create database and table and insert data if it is not created already

--- Create database if not exist
CREATE DATABASE IF NOT EXISTS mariadb_tutorial;

--- Select the database for further operation
USE mariadb_tutorial;

--- Create table if not exist
CREATE TABLE IF NOT EXISTS person (
    id int(12) NOT NULL AUTO_INCREMENT,
    first_name varchar(150) NOT NULL,
    last_name varchar(150),
    email varchar(100),
    age int,
    income double,
    PRIMARY KEY (id)
)

--- Remove old Data
TRUNCATE person;

--- Insert data into table if not exist
INSERT INTO person (first_name, last_name, email, age, income)
VALUES
	('Faiyaz', 'Mia', 'faiyaz@pf.local', 1, 5000),
	('John', 'Doe', 'john@pf.local', 19, 100),
	('Tahsin', NULL, 'tahsin@pf.local', 10, 150),
	('Jane', 'Doe', 'jane@gmail.com', 26, 300),
	('Rakib', 'Mia', 'rakib@bf.local', 24, 200),
	('Sagor', 'Sowrov', 'email10@bf.local', 20, 250),
	('Touhid', NULL, 'hmtmcse.com@gmail.com', 30, 500);


Understanding of MariaDB UPDATE Statement

Syntax

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE conditions;
Here

  • UPDATE : UPDATE is a MariaDB keyword which responsible for update existing data.

  • table_name : Where data will update.

  • SET column1 = value1 : Map the value with column by name.

  • WHERE : WHERE is a keyword of MariaDB as well, which indicate what will be the conditions of data select.

  • conditions : Equal, Not equal, Greater than, Less than etc. conditions.


MariaDB Data tables

+----+------------+-----------+-----------------------+------+--------+
| id | first_name | last_name | email                 | age  | income |
+----+------------+-----------+-----------------------+------+--------+
|  1 | Faiyaz     | Mia       | faiyaz@pf.local       |    1 |   5000 |
|  2 | John       | Doe       | john@pf.local         |   19 |    100 |
|  3 | Tahsin     | NULL      | tahsin@pf.local       |   10 |    150 |
|  4 | Jane       | Doe       | jane@gmail.com        |   26 |    300 |
|  5 | Rakib      | Mia       | rakib@bf.local        |   24 |    200 |
|  6 | Sagor      | Sowrov    | email10@bf.local      |   20 |    250 |
|  7 | Touhid     | NULL      | hmtmcse.com@gmail.com |   30 |    500 |
+----+------------+-----------+-----------------------+------+--------+


MariaDB UPDATE Single Record

Problem: Update Tahsin lastname with Mia, Tahsin id = 3

Syntax

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE conditions;

Example

UPDATE person SET last_name = 'Mia' WHERE id = 3;

Output

Query OK, 1 row affected (0.357 sec)
Rows matched: 1  Changed: 1  Warnings: 0


MariaDB UPDATE Multiple Records

Problem: Update all person lastname to mia whose lastname is Mia

Syntax

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE conditions;

Example

UPDATE person SET last_name = 'mia' WHERE last_name = 'Mia';

Output

Query OK, 3 rows affected (0.369 sec)
Rows matched: 3  Changed: 3  Warnings: 0


MariaDB UPDATE Multiple Records without Condition

Problem: Update all person lastname to Null

Syntax

UPDATE table_name SET column1 = value1, column2 = value2;

Example

UPDATE person SET last_name = NULL;

Output

Query OK, 6 rows affected (0.380 sec)
Rows matched: 7  Changed: 6  Warnings: 0

Note : Without where condition update data is very risky, it can destroy your all data, so before run the command make sure you are confident enough to run this command and the query is error free.


MariaDB UPDATE Statement Video tutorial

Share