EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Software Development Software Development Tutorials MariaDB Tutorial MariaDB UPDATE

MariaDB UPDATE

Sohel Sayyad
Article bySohel Sayyad
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated June 3, 2023

MariaDB UPDATE

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 dependent on the table’s property. When we use the default, we need to use the default keyword. The update command also uses a where clause to specify conditions for the update, and we can also use order by clause to make them in order, the order by clause is optional. We can specify how many rows will be updated using the LIMIT clause in the update command.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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 per our requirement. Finally, we use the where clause to specify the condition for the update command, and order by clause is optional.

How does UPDATE Statement work in MariaDB?

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

Example

Let’s see a different example of how update statements work in MariaDB.

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.

MariaDB UPDATE 1

After that, we need to insert some records by using insert into the 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.

MariaDB UPDATE 2

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

  • Update single column

Suppose we need to change the home name from the above table. At that time, we used 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 the 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.

MariaDB UPDATE 3

  • 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 the 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.

MariaDB UPDATE 4

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 exact 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.

snapshot

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;

snapshot 1

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 in ascending order and descending order.

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

Rules and regulation for using the update

The update command has the following distinct rules.

  1. Update all rows from the table: in which we can update all rows from the specified table.
  2. Update Single row: In this rule, we can update a single row from a specified table.
  3. Update with default: in this rule, we can perform an update command with a default value, but this depends on table properties.
  4. Update Multiple Columns: In this rule, we can update more than one column at the same time.
  5. Using LIMIT clause: We can provide LIMIT to how many rows we need to update from a specified table.
  6. Update Only Specific Rows: With the help of the where clause, we can update only selected rows from specified tables.
  7. 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. This article taught us how and when to use MariaDB update statements.

Recommended Articles

We hope that this EDUCBA information on “MariaDB UPDATE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MariaDB vs MySQL
  2. MariaDB vs MongoDB
  3. DBMS vs File System
  4. Database Management Software
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
CYBER SECURITY & ETHICAL HACKING Course Bundle - 13 Courses in 1 | 3 Mock Tests
64+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
62+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
KALI LINUX Course Bundle - 6 Courses in 1
20+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MARIADB Course Bundle - 5 Courses in 1
 11+ Hours of HD Videos
5 Courses
Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more