EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials MariaDB Tutorial MariaDB UPDATE
Secondary Sidebar
MariaDB Tutorial
  • MariaDB
    • MariaDB Versions
    • MariaDB? list users
    • MariaDB Commands
    • MariaDB odbc
    • MariaDB Workbench
    • MariaDB for windows
    • MariaDB Server
    • MariaDB? Data Types
    • MariaDB? boolean
    • MariaDB phpMyAdmin
    • MariaDB Mysqldump
    • MariaDB Java Connector
    • MariaDB insert
    • MariaDB UPDATE
    • MariaDB? rename column
    • MariaDB AUTO_INCREMENT
    • MariaDB Timezone
    • MariaDB GROUP_CONCAT
    • MariaDB wait_timeout
    • MariaDB MaxScale
    • MariaDB? with
    • MariaDB GUI
    • MariaDB? create?table
    • MariaDB? SHOW TABLES
    • MariaDB alter table
    • MariaDB List Tables
    • MariaDB JSON Functions
    • MariaDB Foreign Key
    • MariaDB? trigger
    • MariaDB Grant All Privileges
    • MariaDB Select Database
    • MariaDB? create database
    • MariaDB Delete Database
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • MariaDB List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • MariaDB Max Connections
    • MariaDB show users
    • MariaDB Delete User
    • MariaDB? change user password
    • MariaDB? change root password
    • MariaDB reset root password
    • MariaDB IF
    • MariaDB bind-address
    • MariaDB Transaction
    • MariaDB Cluster
    • MariaDB Logs
    • MariaDB Encryption
    • MariaDB? backup
    • MariaDB Replication
    • MariaDB max_allowed_packet
    • MariaDB? performance tuning
    • MariaDB export database
    • MariaDB? import SQL

MariaDB UPDATE

By Sohel SayyadSohel Sayyad

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

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:

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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.

MariaDB UPDATE 1

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.

MariaDB UPDATE 2

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.

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

All in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,754 ratings)

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.

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;

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

  1. Update all rows from 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 update command with default value but this is dependent 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 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. From this article, we learned how and when we use MariaDB update statements.

Recommended Articles

This is a guide to MariaDB UPDATE. Here we discuss the definition, syntax, How does UPDATE statement works in MariaDB?, and Examples with code implementation. You may also have a look at the following articles to learn more –

  1. MariaDB vs MySQL
  2. MariaDB vs MongoDB
  3. DBMS vs File System
  4. Database Management Software
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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

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

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

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

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

EDUCBA Login

Forgot Password?

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

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

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

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

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

Let’s Get Started

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