MariaDB Update Statement with Examples

In this MariaDB tutorial, we are going to learn about the MariaDB Update statement. Additionally, we will cover the following topics.

• MariaDB Update Statement
• Update Multiple Columns in MariaDB
• How to Update One Table From Another in MariaDB
• MariaDB Update Multiple Tables
• How to Update Multiple Rows in MariaDB
• MariaDB Update Root Password
• How to Update User Password in MariaDB
• MariaDB Update From Select
• MariaDB Update Column Type
• How to Update Column Name in MariaDB
• MariaDB Update Date
• MariaDB Update Datetime
• MariaDB Duplicate Key Update
• MariaDB Update Exists
• MariaDB Update Primary Key
• How to Update Password in MariaDB
• MariaDB Update View
• MariaDB Update Vs replace
• MariaDB Update Where
• MariaDB Update Returning
• MariaDB Update Ignore
• MariaDB Update Order By
• MariaDB Update JSON Value
• MariaDB Update NULL Value

MariaDB Update Statement

Here, we will learn about MariaDB Update Statement and its syntax with some examples.

The MariaDB Update query is used to update the already existing records present in the table. There are three syntaxes for the update query in MariaDB.

1. Update statement to update a single MariaDB table
2. Update statement to update multiple MariaDB tables
3. Update statement to update one table from another

Next, let’s discuss these syntaxes for the Update statement in MariaDB.

Syntax 1 – Syntax for Update statement in MariaDB for updating a single table.

UPDATE table
SET col1 = exp1,
    col2 = exp2,
    ...
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
[LIMIT number_rows];

Syntax 2 – Syntax for MariaDB Update statement for updating multiple table.

UPDATE table1, table2, ... 
SET col1 = exp1,
    col2 = exp2,
    ...
WHERE table1.col = table2.col
AND conditions;

Syntax 3 – Syntax for MariaDB Update statement for updating one table from another table.

UPDATE table1
SET col1 = (SELECT exp1
               FROM table2
               WHERE conditions)
[WHERE conditions];

The parameters and arguments used in the syntaxes are as follows.

• col1, col2 – Columns you want to update.
• exp1, exp2- They are the new values that you want to assign to col1 and col2 respectively.
• WHERE conditions – It is optional to use during updating and it is the conditions that should be met for the execution of the update.
• ORDER BY expression – It is optional to use during updating and it is used to sort the records after updating meanwhile limiting to the no. of records.
• LIMIT number_rows – It is optional to use during updating and if the limit is provided, it is used to limit the no. of records updated.

Examples corresponding to each Syntax

Example 1 – Updating Single column.

UPDATE cities
SET city_name = 'Las Vegas'
WHERE city_name = 'Los Angeles';

In the above example, we are updating the value of the city_name column from Los Angeles to Las Vegas.

After successfully updating the cities table, we can check the result using the SELECT statement.

Example 2 – Update multiple columns.

UPDATE cities
SET city_name = 'New York',
    country_name = 'USA'
WHERE state_name = 'California';

In this example, we are updating the value of 2 columns from the cities table. First, we are changing the value of the city_name column and second, we are changing the value of the country_name column.

Example 3 – Update the table with data from another table.

UPDATE places
SET place_name = (SELECT city_name
               FROM cities
               WHERE city_id = 1)
WHERE place_id = 1;

Here, we are updating the data of one table using another table where the place_name in places table is being updated from city_name where city_id is 1.

Example 4 – Update multiple tables.

UPDATE cities, places
SET cities.state_name = places.place_name
WHERE cities.city_id = places.place_id
AND cities.city_id > 2;

Here, in this example, we are updating the data of multiple tables. And the value of state_name is being updated from place_name from place table where city_id from cities table is same as place_id from place table under a condition if city_id from cities table should be greater than 2.

Also, check: How to Remove User in MariaDB

MariaDB Update Multiple Columns

In this, we will learn about the MariaDB Update Multiple columns, its syntax with some examples.

The MariaDB Update query is used to update the already existing records present in the table. And here, you need to update multiple columns of the same table.

The syntax for the MariaDB Update Multiple Columns is as follows.

Syntax – Syntax for MariaDB Update Multiple Columns in one table.

UPDATE table
SET col1 = exp1,
    col2 = exp2,
    ...
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
[LIMIT number_rows];

The parameters and arguments used in the syntax are as follows.

• col1, col2 – Columns you want to update.
• exp1, exp2- They are the new values that you want to assign to col1 and col2 respectively.
• WHERE conditions – It is optional to use during updating and it is the conditions that should be met for the execution of the update.
• ORDER BY expression – It is optional to use during updating and it is used to sort the records after updating meanwhile limiting to the no. of records.
• LIMIT number_rows – It is optional to use during updating and if the limit is provided, it is used to limit the no. of records updated.

For Example,

UPDATE cities
SET city_name = 'New York',
    country_name = 'USA'
WHERE city_name = 'California';

Here, the update is in the multiple columns where city_name is being updated from California to New York along with country_name i.e. USA.

Also, read: How to Grant All Privileges in MariaDB

MariaDB Update One Table From Another

In this, you will learn about the MariaDB Update One Table From Another, its syntax with example.

The MariaDB Update query is used to update the already existing records present in the table. And here, you need to update One Table From Another table.

The syntax for the MariaDB Update One Table From Another is as follows.

Syntax – Syntax for MariaDB Update statement for updating one table from another table.

UPDATE table1
SET col1 = (SELECT exp1
               FROM table2
               WHERE conditions)
[WHERE conditions];

The parameters and arguments used in the syntax are as follows.

• col1 – Columns you want to update.
• exp1 – They are the new values that you want to assign to col1 and col2 respectively.
• table1, table2 – table2 is the second table whose data would be updated in table1.
• WHERE conditions – It is optional to use during updating and it is the conditions that should be met for the execution of the update.

For Example,

UPDATE places
SET place_name = (SELECT city_name
               FROM cities
               WHERE city_id = 1)
WHERE place_id = 1;

Here, the update is from one table to another table where the place_name in places table is being updated from city_name where city_id is 1.

Read: How to Create View in MariaDB

MariaDB Update Multiple Tables

In this, you will learn about the MariaDB Update Multiple Tables, its syntax, and an example.

The MariaDB Update query is used to update the already existing records present in the table.

The syntax for the MariaDB Update Multiple Tables is as follows.

Syntax – Syntax for MariaDB Update statement for updating multiple tables.

UPDATE table1, table2, ... 
SET col1 = exp1,
    col2 = exp2,
    ...
WHERE table1.col = table2.col
AND conditions;

The parameters and arguments used in the syntax are as follows.

• col1, col2 – Columns you want to update.
• exp1, exp2- They are the new values that you want to assign to col1 and col2 respectively.
• table1, table2 – table2 is the second table whose data would be updated in table1.
• WHERE conditions – It is optional to use during updating and it is the conditions that should be met for the execution of update.

For Example,

UPDATE cities, places
SET cities.state_name = places.place_name
WHERE cities.city_id = places.place_id
AND cities.city_id > 2;

Here, the update is in multiple tables where place_name is being updated from place_name from place table where city_id from cities table is same as place_id from place table under a condition if city_id from cities table should be less than 2.

Read: Replace Function in MariaDB

MariaDB Update Multiple Rows

In this, you will learn about the MariaDB Update Multiple Rows, its syntax with some examples.

The MariaDB Update statement is used along with the CASE WHEN ELSE statement to update the already existing records present in the table.
The CASE WHEN ELSE statement updates the matched case and ends otherwise.

The syntax for the MariaDB Update Multiple Rows is as follows.

UPDATE 'table_name' 
SET 'col1' = CASE 'col2'
WHEN 'x' THEN 'value y'
.
.
.
.

ELSE 'column'
END

Here, table_name is the name of the table on which you are doing the update and column1 is the column in which the update process is taking place corresponding to column2.

To update multiple rows, you need to type the following command.

UPDATE cities SET country_name = CASE 
WHEN id=1 THEN 'US'
WHEN id=2 THEN 'US'
WHEN id=3 THEN 'US'
WHEN id=6 THEN 'US'
ELSE country_name
END;

Here, you are updating the country_name column corresponding to the following id using the CASE WHEN ELSE statement.

This is how you can update multiple rows using a single query.

Also check: MariaDB import CSV

MariaDB Update Root Password

In this, you will learn about MariaDB Update Root Password using the SET PASSWORD statement.

The SET PASSWORD statement assigns a password to an already existing MariaDB user/root account.

The Syntax for the SET PASSWORD statement is as follows.

SET PASSWORD FOR 'User'@'localhost' = PASSWORD('password123');

Here, the User is the name of the user whose password you want to change and password123 is the new password.

Now, you need to change the password for a user named Root then you can type the following command.

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root123');

Note – You can also remove the user password by using the SET PASSWORD statement and the syntax for doing is given below.

SET PASSWORD FOR 'User'@'localhost' = OLD_PASSWORD('password123');

Here, the User is the name of the user whose password you want to remove and password123 is the old password.

For example,

SET PASSWORD FOR 'root'@'localhost' = OLD_PASSWORD('root123');

Using this command, the password for Root can be removed.

So, this is how we can Update the Root Password Using the SET PASSWORD statement.

Also, read: MariaDB Timestamp

MariaDB Update User Password

In this, you will learn about MariaDB Update User Password using the SET PASSWORD statement.

The SET PASSWORD statement assigns a password to an already existing MariaDB user account.

The Syntax for the SET PASSWORD statement is as follows.

SET PASSWORD FOR 'User'@'localhost' = PASSWORD('password123');

Here, the User is the name of the user whose password you want to change and password123 is the new password.

Now, you need to change the password for a user named Jeny then you can type the following command.

SET PASSWORD FOR 'Jeny'@'localhost' = PASSWORD('jeny123');

Note – You can also remove the user password by using the SET PASSWORD statement and the syntax for doing is given below.

SET PASSWORD FOR 'User'@'localhost' = OLD_PASSWORD('password123');

Here, the User is the name of the user whose password you want to remove and password123 is the old password.

For example,

SET PASSWORD FOR 'Jeny'@'localhost' = OLD_PASSWORD('Jeny123');

Using this command, the password for Jeny can be removed.

So, this is how we can Update the User Password Using the SET PASSWORD statement.

Read: MariaDB ERROR 1064

MariaDB Update From Select

In this, you will learn about MariaDB Update using the SELECT statement.

Using the Update statement with Select statement updates several rows by selecting several rows under a condition.

Here, is an example to illustrate the use of the Update statement along with the Select statement.

UPDATE places
SET place_name = (SELECT city_name
               FROM cities
               WHERE city_id = 1)
WHERE place_id = 1;

Here, the update is from one table to another table where the place_name in places table is being updated from city_name where city_id is 1.

This is how you can MariaDB Update using the SELECT statement.

MariaDB Update Column Type

Here, you are going to learn about how to Update Column Type in MariaDB along with syntax and an example.

To update the column type, we need to use Alter Table statement which will change the column type.

The syntax for updating column type is as follows.

ALTER TABLE table_name
  MODIFY col_name col_def
    [ FIRST | AFTER col_name ];

Here, table_name is the name of the table we want to modify, col_name is the name of the column we want to modify in the table, col_def is the modified data type and the definition of the column, and FIRST | AFTER col_name is optional and it tells MariaDB wherein the table to position the column if you wish to change its position.

To update column type, type the following command.

ALTER TABLE cities
  MODIFY country_name varchar(50);

Here, cities is the name of the table you want to modify and country_name is the name of the column whose column type you want to change to varchar(50).

This is how you can update the column type.

Read: How to Create Function in MariaDB

MariaDB Update Column Name

Here, you are going to learn about how to Update Column Names in MariaDB along with syntax and an example.

To update the column type, we need to use Alter Table statement which will change the column name.

The syntax for updating column type is as follows.

ALTER TABLE table_name
  CHANGE COLUMN old_name new_name 
    col_def
    [ FIRST | AFTER col_name ]

• Here, table_name is the name of the table we want to modify, old_name is the name of the column we want to modify in the table, and new_name is the new name of the column after this command will be executed.
• Moreover, col_def is the modified data type and the definition of the column.
• And FIRST | AFTER col_name is optional and it tells MariaDB wherein the table to position the column if you wish to change its position.

To update the column name, type the following command.

ALTER TABLE cities
  CHANGE COLUMN city_name cname
    varchar(50);

Here, cities is the name of the table you want to modify, city_name is the name of the column whose column name you want to change to cname.

This is how you can update the column name.

Read: How to Create Trigger in MariaDB

MariaDB Update Date

Here, you are going to learn about how to Update Column Date in MariaDB along with syntax and an example.

To update the column type, we need to use the Update Table statement which will change the date.

The syntax for updating Date is as follows.

UPDATE table   
SET col_name = 'YYYY-MM-DD'  
WHERE Id = value

Here, col_name is the name of the column in which you want to update Date and Id is the corresponding column that eases the search for the row you want to update.

To update the Date, type the following command.

UPDATE table   
SET EndDate = '2014-03-16'   
WHERE Id = 1

Here, EndDate is the column corresponding at Id = 1 in which we need to update Date.

This is how you can update the Date in MariaDB.

MariaDB Update Datetime

Here, you are going to learn about how to Update Column Datetime in MariaDB along with syntax and an example.

To update the column type, we need to use the Update Table statement which will change the DateTime.

The syntax for updating DateTime is as follows.

UPDATE table   
SET col_name = 'YYYY-MM-DD HH:MM:SS'  
WHERE Id = value

Here, col_name is the name of the column in which you want to update DateTime and Id is the corresponding column that eases the search for the row you want to update.

To update the DateTime, type the following command.

UPDATE table   
SET EndDate = '2014-03-16 00:00:00.000'   
WHERE Id = 1

Here, EndDate is the column corresponding at Id = 1 in which we need to update DateTime.

This is how you can update the DateTime.

Read How to Drop Column from MariaDB Table

MariaDB Duplicate Key Update

Duplicate Key Update is the MariaDB/MySQL extension to the insert statement that finds a duplicate unique or primary key that performs UPDATE.

Whenever a new record with an already existing key is inserted, there will always be an error occurred. So, to overcome this you can use Duplicate Key Update.

Here, Insert Into statement implements Duplicate Key Update.

Here, you can see that there is an error that occurred because of the insertion of a record with a duplicate key.

CREATE TABLE usa_states
(
state_id  INT PRIMARY KEY,
state_name VARCHAR(50) NOT NULL,
state_code VARCHAR(2)
);
 
INSERT INTO usa_states
VALUES (101, 'Alaska', 'AK'),
(102, 'Arizona', 'AZ'),
(103, 'California', 'CA');

In the above query, first, we created a table in MariaDB named usa_states. This table consists of 3 columns. Out of these 3 columns, the state_id is a PRIMARY KEY column. After creating a table, we are using the INSERT INTO statement to insert 3 records within the table. Here is the view of the table.

Now, when we try to insert another value in the table but with the same primary key value, the MariaDB server will return an error.

But, we can easily overcome this error in MariaDB by using the Insert on Duplicate Key Update statement. And for this implementation, the code is given below.

INSERT INTO usa_states VALUES (101,'Florida', 'FL') 
ON DUPLICATE KEY UPDATE state_name='Florida', state_code='FL';

In the above example, we are using the DUPLICATE KEY UPDATE statement to update the state_name from “Alaska” to “Florida” and also update the state_code from “AK” to “FL“.

To verify the changes in the table, type the following command.

SELECT * FROM usa_states;

From the above result, we can observe that Duplicate Key Update has updated the new record corresponding to the same key.

MariaDB Update Exists

In this, you are going to learn about MariaDB Update Exists.

The MariaDB UPDATE EXISTS condition is used in combination with a subquery and is considered “to be met” if the subquery updates at least one row. The Exists condition can also be used with SELECT, INSERT, or DELETE statements.

You can use MariaDB Update Exists statement by typing the following command.

SELECT *
FROM cities
WHERE EXISTS (SELECT *
              FROM places
              WHERE places.place_id = cities.city_id);

This is how you can use MariaDB Update Exists statement.

Read How to Add Column in MariaDB

MariaDB Update Primary Key

Here, you are going to learn about How to Update Primary Key in MariaDB.

In MariaDB, a primary key is usually a single field or combination of fields that uniquely identifies a record. The primary key can never contain a NULL value in any of their fields.
To update a primary key, firstly you need to drop the already existing primary key and then define a new primary key using Alter statement.

To drop a primary key, you need to type the following command.

ALTER TABLE tableName DROP PRIMARY KEY;

 To create a new primary key using Alter statement, type the following command.

ALTER TABLE tableName ADD PRIMARY KEY (new_id);

This is how you can update a primary key in MariaDB.

MariaDB Update Password

In this, you will learn about MariaDB Update Password using the SET PASSWORD statement.

The SET PASSWORD statement assigns a password to an already existing MariaDB user/root account.

The Syntax for the SET PASSWORD statement is as follows.

SET PASSWORD FOR 'User'@'localhost' = PASSWORD('password123');

Here, the User is the name of the user whose password you want to change and password123 is the new password.

Now, you need to change the password for a user named Jeny/root then you can type the following command.

SET PASSWORD FOR 'Jeny'@'localhost' = PASSWORD('jeny123');

Note – You can also remove the user password by using the SET PASSWORD statement and the syntax for doing is given below.

SET PASSWORD FOR 'User'@'localhost' = OLD_PASSWORD('password123');

Here, the User is the name of the user whose password you want to remove and password123 is the old password.

For example,

SET PASSWORD FOR 'Jeny'@'localhost' = OLD_PASSWORD('jeny123');

Using this command, the password for Jeny can be removed.

So, this is how we can Update the Password Using the SET PASSWORD statement.

Read How to Create Table in MariaDB

MariaDB Update View

Here, you will learn about How to Update the View using the syntax along with an example.

View offers more security and simplicity over data tables. In fact, you can easily update views in multiple ways. 

To Update views in MariaDB, you need to first create a view and then use ALTER statement and can make changes/updates in the view.

To create a view in MariaDB, type the following command.

create view city_view as
select city_id,cname
from cities;

Now, to make changes or updates, type the following command.

alter view city_view as
select city_id,cname
from cities
where city_id>1;

This is how you can update the views in MariaDB.

MariaDB Update Vs replace

Here, you will learn about the differences between Update Statement and Replace Function.

Firstly, you will learn about the Update Statement.

The MariaDB Update query is used to update the already existing records present in the table. There are three syntaxes for the update statement in MariaDB.

One of the basic syntax for the Update statement is as follows.

UPDATE table1
SET col1 = (SELECT exp1
               FROM table2
               WHERE conditions)
[WHERE conditions];

An illustration of the syntax is given below.

UPDATE cities
SET city_name = 'New York',
    country_name = 'USA'
WHERE city_name = 'California';

Now, let’s learn about Replace function.

The REPLACE function replaces all occurrences of a specified string and the syntax for the same is as follows.

REPLACE( string, from_substring, to_substring );

Here are some examples of their output.

SELECT REPLACE('XYZ.com', '.com', ' knows MariaDB!');
Result: 'XYZ knows MariaDB!'

SELECT REPLACE('XYZ.com', '.COM', ' knows MariaDB!');
Result: 'XYZ.com'

SELECT REPLACE('abc abc', 'b', 'X');
Result: 'aXc aXc'

SELECT REPLACE('abc abc', 'B', 'X');
Result: 'abc abc'

SELECT REPLACE('12345 12345', 1, 9);
Result: '92345 92345'

These are the major differences between the update statement and replace function.

Also, check: How to Create Table in MariaDB

MariaDB Update Where

Here, you will learn about MariaDB Update Where with its syntax and an example.

The Where statement along with the Update statement selects several rows satisfying a condition and then updates it.

The syntax for the Update statement along with the Where statement is given below.

UPDATE table
SET col1 = exp1,
    col2 = exp2,
    ...
[WHERE conditions];

And the illustration related to this is as follows.

UPDATE cities
SET city_name = 'Las Vegas'
WHERE city_name = 'California';

This is how you can use the Where statement along with the Update statement.

MariaDB Update Returning

The RETURNING clause in MariaDB is quite well utilized while inserting or replacing values from a MariaDB table. And the key role of RETURNING option is to return the inserted or replaced records.

However, in MariaDB, we cannot use the RETURNING option with the UPDATE statement. As the UPDATE statement is simply used to update the existing table data.

MariaDB Update Ignore

In this section, we will illustrate how to use the UPDATE IGNORE statement in MariaDB. As the name specifies itself, an IGNORE option is utilized with an UPDATE statement to ignore certain common errors associated with an update operation.

Let’s understand its usability with the help of an example in MariaDB. And for this task, we are going to create the following table.

CREATE TABLE country (
	country_id INT PRIMARY KEY,
	country_name VARCHAR(50),
	country_code VARCHAR(50)
);

INSERT INTO country (country_id, country_name, country_code) 
VALUES (1, 'United States', 'US'),
       (2, 'Canada', 'CA'),
       (3, 'Australia', 'AU'),
       (4, 'New Zealand', 'NZ');

In the above SQL code, first, we are creating a table named country using CREATE TABLE statement in MariaDB. And for the table definition, we have specified 3 columns out of which country_id is a unique primary key column.

After this, we are using the INSERT statement to insert some country records in the table. For example, the United States, Canada, Australia, etc.

Now, there is one common error related to the UPDATE statement in MariaDB that we cannot have duplicate primary key values. The example of this error is illustrated below.

UPDATE country 
SET country_id = 4
WHERE country_id = 2;

In the above example, we are trying to set the value of country_id as 4 where the country_id is 2. But, the coutry_id with value 4 already exists in the table. In this case, the server will return the following error.

Now, to handle the above error, we can use the UPDATE IGNORE statement instead of using the UPDATE statement alone. Here is the SQL code for this implementation.

UPDATE IGNORE country 
SET country_id = 4
WHERE country_id = 2;

Now, when we execute the above query, the server will not return any error. However, it will also not affect the country table.

MariaDB Update Order By

In this section, we will understand how to use the ORDER BY clause while using the UPDATE statement in MariaDB.

The UPDATE statement in MariaDB is utilized to update the data of a database table. On the other side, the ORDER BY clause is utilized to arrange the order of the returning resultset. However, we can also use the ORDER BY clause in combination with LIMIT in the UPDATE statement.

Let’s understand this implementation using an example in MariaDB.

CREATE TABLE countryStatetbl (
	country_id INT,
	country_name VARCHAR(50),
	state_name VARCHAR(50),
	country_code VARCHAR(50)
);

INSERT INTO countryStatetbl (country_id, country_name, state_name, country_code) 
VALUES (1, 'United States', 'Washington','US'),
       (1, 'United States', 'California','US'),
       (1, 'United States', 'California','US'),
       (2, 'Canada', 'Alberta','CA'),
       (3, 'Australia', 'New South Wales','AU'),
       (4, 'New Zealand', NULL, 'NZ');

In the above query, first, we are creating a table in MariaDB named countryStatetbl with 4 columns. And we are also using the INSERT statement to insert some records in the table. Here is the table after adding the data.

However, by mistake, we have inserted the same record 2 twice in the table. So, to update the data, we will use the UPDATE statement with the ORDER BY clause. Here is the query for this updation.

UPDATE countryStatetbl 
SET state_name = 'Florida'
WHERE state_name = 'California'
ORDER BY state_name ASC LIMIT 1;

In the above query, we are using an UPDATE statement to update the value of state_name where state_name is defined as California. And to restrict the result, we are using the ORDER BY clause with LIMIT.

Here is the final result after updating the state_name in the table.

MariaDB Update Null Value

In this section, we will discuss how to update the null value in a MariaDB table to some specific values. This execution does not involve any special syntax, so we will illustrate this topic using an example in MariaDB.

CREATE TABLE countryStatetbl (
	country_id INT,
	country_name VARCHAR(50),
	state_name VARCHAR(50),
	country_code VARCHAR(50)
);

INSERT INTO countryStatetbl (country_id, country_name, state_name, country_code) 
VALUES (1, NULL, 'Washington','US'),
       (1, NULL, 'California','US'),
       (1, NULL, 'California','US'),
       (2, 'Canada', 'Alberta','CA'),
       (3, 'Australia', 'New South Wales','AU'),
       (4, 'New Zealand', NULL, 'NZ');

In the above query, first, we are creating a table in MariaDB named countryStatetbl with 4 columns. After this, we are using the INSERT INTO statement to insert some records in the table. Here is the table after adding the data.

However, the above table consists of country_name as NULL in place of the United States. So, we will try to update the values of the table using an UPDATE statement.

UPDATE countrystatetbl
SET country_name = 'United States'
WHERE country_name IS NULL;

By using the above UPDATE statement, we are setting the value of the country_name column as the United States where their value is already NULL. And after updating the data, we will get the following table result.

MariaDB Update JSON Value

In this section, we will discuss how to update a JSON data type in MariaDB using the UPDATE TABLE statement. Again, for this implementation there is no special syntax, we can easily update the value of the JSON field using the UPDATE statement. However, to understand the implementation, we will illustrate an example.

CREATE TABLE Employee(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(50),
country VARCHAR(50),
info JSON
);

INSERT INTO Employee(NAME, country, info)
VALUES('Adam', 'United States', '{"emp_id":101, "dept_id":201}'),
      ('James', 'United States', '{"emp_id":102, "dept_id":205}'),
      ('Rogers', 'Canada', '{"emp_id":102, "dept_id":201, "country_code":"CA"}'),
      ('Robert', 'Canada', '{"emp_id":103, "dept_id":205, "country_code":"CA"}'),
      ('John', 'New Zealand', '{"emp_id":304, "dept_id":203, "country_code":"NZ"}');

In the above example, first, we have created a table named employee with 4 columns out of which info is JSON data type. After this, we are using the INSERT statement to add some data to the table. In the end, we will get the following table result.

However, for the first 2 records, we forgot to add the country_code in the info column. So, now, we will use the UPDATE statement to update the value of the info column.

UPDATE employee
SET info = JSON_SET(info, '$.country_code', 'US')
WHERE country = 'United States';

In the above query, we are using the UPDATE statement and JSON_SET function to update the value of the info column where the value of country column is the United States. Now, after execution, we will get the following updated result.

You may like the following MariaDB tutorials:

• MariaDB Logs – Helpful Guide
• How to Grant User Access to a MariaDB Database

In this MariaDB Tutorial, we are going to learn about the MariaDB Update statement. Additionally, we will cover the following topics.

• MariaDB Update Statement
• Update Multiple Columns in MariaDB
• How to Update One Table From Another in MariaDB
• MariaDB Update Multiple Tables
• How to Update Multiple Rows in MariaDB
• MariaDB Update Root Password
• How to Update User Password in MariaDB
• MariaDB Update From Select
• MariaDB Update Column Type
• How to Update Column Name in MariaDB
• MariaDB Update Date
• MariaDB Update Datetime
• MariaDB Duplicate Key Update
• MariaDB Update Exists
• MariaDB Update Primary Key
• How to Update Password in MariaDB
• MariaDB Update View
• MariaDB Update Vs replace
• MariaDB Update Where
• MariaDB Update Returning
• MariaDB Update Ignore
• MariaDB Update Order By
• MariaDB Update JSON Value
• MariaDB Update NULL Value