Definition of MariaDB UPDATE Statement

MariaDB provides the update facility to users like MySQL, Update command is used to modify the content of columns. The update command uses the SET clause to specify the column name for modification and assign a new value for that field. The assigned value can by default value or some expression that is dependent on the property of the table. When we use default so at that time we need to use the default keyword. The update command also uses a where clause to specify conditions for update and we can also use order by clause to make them in order, the order by clause is optional. In the update command, we can also specify how many rows will be updated by using the LIMIT clause.

SQL Training Program (7 Courses, 8+ Projects)
7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (21 ratings)

View Course

Syntax:

update table name set column 1 field = new assigned value,column 2 field = new assigned value,........... column N field = new assigned value where [Condition.......] order by

Explanation:

In the above syntax, we use the update command followed by the table name as shown in the above syntax. After that, we need to specify the column field with the assigned new value as per our requirement. Finally, we use where clause to specify the condition for update command and order by clause is optional.

How does UPDATE Statement work in MariaDB?

Basically, we need exiting data or table to perform update command in MariaDB. For update purposes we need different clauses such as SET, WHERE and LIMIT clause and we can use them as per our requirement. The LIMIT and order by clause is an optional part of the update statement. Update command works for single column field updating as well as multiple column updating.

Example

Let’s see a different example to understand how update statements work in MariaDB as follows.

First, we need to create table busing following the create a statement as follows.

create table home_table( home_id int not null auto_increment, home_name varchar(100) not null, address varchar(40) not null, owner_name varchar(50) not null, primary key ( home_id ) );

Explanation:

For example, we created a table name as home_table with different attributes such as home_id, home_name, address, and owner_name with different data types as shown in the above statement. The result of the above statement we illustrate by using the following snapshot.

After that, we need to insert some records by using insert into statement as follows.

insert into home_table ( home_name, address, owner_name) values ("Skylight", "London", "Jenny"), ("Moon", "Mumbai", "John"), ("Sunshine", "Kolkatta", "Sam"); select * from home_table;

Explanation:

With the help of the above statement, we inserted some records into the home_table. The result of the above statement we illustrate by using the following snapshot.

Now we have a table with records so we can perform update commands as follows.

Update single column

Suppose we need to change the home name from the above table. At that time we use the following statement as follows.

update home_table set home_name = 'White_House' where home_id = 1;

Explanation:

In the above example, we use an update statement to update the value of the thehome_name column, here we need to change home_name of 1 such as White_House instead of Skylight at that time we use the above statement. The result of the above statement we illustrate by using the following snapshot.

Update Multiple Column

Let’s see how we can update multiple columns at the same time in MariaDB as follows.

update home_table set home_name = 'Maria', address = 'US' where home_id = 2;

Explanation:

In the above example, we updated two columns at the same time see here statement start with update command with table name home_table followed by modified values of both column names with new assigned values as shown above statement and finally we use where clause to specify the condition. The result of the above statement we illustrate by using the following snapshot.

Now let’s see how we can use the LIMIT clause in the update statement as follows.

update home_table set address = "UAE" limit 2;

Explanation:

In the above example, we use the LIMIT clause to set the same value of two rows as shown in the above-mentioned statement. Here we set UAE values to row 1 and 2. The result of the above statement we illustrate by using the following snapshot.

Now let’s see how we can use order by clause in the update statement as follows.

update table name set column name =new assigned value where [condition…..] order by;

Explanation:

In the above syntax, we can use the update statement we set, where, and order by clause as shown in the statement. With the help of order by clause, we can arrange column by ascending order as well as descending order.

So in this way, we can use update statements by using different clauses as well as we can use different rules such as with expression and with default values, etc.

Rules and regulation for using the update

Update command has different rules as follows

Update all rows from table: in which we can update all rows from the specified table.
Update Single row: In this rule, we can update a single row from a specified table.
Update with default: in this rule, we can perform update command with default value but this is dependent on table properties.
Update Multiple Columns: In this rule, we can update more than one column at the same time.
Using LIMIT clause: We can provide LIMIT to how many rows we need to update from a specified table.
Update Only Specific Rows: With the help of where clause we can update only selected rows from specified tables.
Update with value and expression: In this rule, we can update the value of a specific column with some mathematical expression.

Conclusion

We hope from this article you have understood the MariaDB update statement. From the above article, we have learned the basic syntax of MariaDB update statements and we also see different examples of MariaDB update statements with different clauses. We also learned the rules of MariaDB update statements. From this article, we learned how and when we use MariaDB update statements.

Popular Course in this category