- MySQL Basics
- MySQL - Home
- MySQL - Introduction
- MySQL - Features
- MySQL - Versions
- MySQL - Variables
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Node.js Syntax
- MySQL - Java Syntax
- MySQL - Python Syntax
- MySQL - Connection
- MySQL - Workbench
- MySQL Databases
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Show Database
- MySQL - Copy Database
- MySQL - Database Export
- MySQL - Database Import
- MySQL - Database Info
- MySQL Users
- MySQL - Create Users
- MySQL - Drop Users
- MySQL - Show Users
- MySQL - Change Password
- MySQL - Grant Privileges
- MySQL - Show Privileges
- MySQL - Revoke Privileges
- MySQL - Lock User Account
- MySQL - Unlock User Account
- MySQL Tables
- MySQL - Create Tables
- MySQL - Show Tables
- MySQL - Alter Tables
- MySQL - Rename Tables
- MySQL - Clone Tables
- MySQL - Truncate Tables
- MySQL - Temporary Tables
- MySQL - Repair Tables
- MySQL - Describe Tables
- MySQL - Add/Delete Columns
- MySQL - Show Columns
- MySQL - Rename Columns
- MySQL - Table Locking
- MySQL - Drop Tables
- MySQL - Derived Tables
- MySQL Queries
- MySQL - Queries
- MySQL - Constraints
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Replace Query
- MySQL - Insert Ignore
- MySQL - Insert on Duplicate Key Update
- MySQL - Insert Into Select
- MySQL Indexes
- MySQL - Indexes
- MySQL - Create Index
- MySQL - Drop Index
- MySQL - Show Indexes
- MySQL - Unique Index
- MySQL - Clustered Index
- MySQL - Non-Clustered Index
- MySQL Operators and Clauses
- MySQL - Where Clause
- MySQL - Limit Clause
- MySQL - Distinct Clause
- MySQL - Order By Clause
- MySQL - Group By Clause
- MySQL - Having Clause
- MySQL - AND Operator
- MySQL - OR Operator
- MySQL - Like Operator
- MySQL - IN Operator
- MySQL - ANY Operator
- MySQL - EXISTS Operator
- MySQL - NOT Operator
- MySQL - NOT EQUAL Operator
- MySQL - IS NULL Operator
- MySQL - IS NOT NULL Operator
- MySQL - Between Operator
- MySQL - UNION Operator
- MySQL - UNION vs UNION ALL
- MySQL - MINUS Operator
- MySQL - INTERSECT Operator
- MySQL - INTERVAL Operator
- MySQL Joins
- MySQL - Using Joins
- MySQL - Inner Join
- MySQL - Left Join
- MySQL - Right Join
- MySQL - Cross Join
- MySQL - Full Join
- MySQL - Self Join
- MySQL - Delete Join
- MySQL - Update Join
- MySQL - Union vs Join
- MySQL Keys
- MySQL - Unique Key
- MySQL - Primary Key
- MySQL - Foreign Key
- MySQL - Composite Key
- MySQL - Alternate Key
- MySQL Triggers
- MySQL - Triggers
- MySQL - Create Trigger
- MySQL - Show Trigger
- MySQL - Drop Trigger
- MySQL - Before Insert Trigger
- MySQL - After Insert Trigger
- MySQL - Before Update Trigger
- MySQL - After Update Trigger
- MySQL - Before Delete Trigger
- MySQL - After Delete Trigger
- MySQL Data Types
- MySQL - Data Types
- MySQL - VARCHAR
- MySQL - BOOLEAN
- MySQL - ENUM
- MySQL - DECIMAL
- MySQL - INT
- MySQL - FLOAT
- MySQL - BIT
- MySQL - TINYINT
- MySQL - BLOB
- MySQL - SET
- MySQL Regular Expressions
- MySQL - Regular Expressions
- MySQL - RLIKE Operator
- MySQL - NOT LIKE Operator
- MySQL - NOT REGEXP Operator
- MySQL - regexp_instr() Function
- MySQL - regexp_like() Function
- MySQL - regexp_replace() Function
- MySQL - regexp_substr() Function
- MySQL Fulltext Search
- MySQL - Fulltext Search
- MySQL - Natural Language Fulltext Search
- MySQL - Boolean Fulltext Search
- MySQL - Query Expansion Fulltext Search
- MySQL - ngram Fulltext Parser
- MySQL Functions & Operators
- MySQL - Date and Time Functions
- MySQL - Arithmetic Operators
- MySQL - Numeric Functions
- MySQL - String Functions
- MySQL - Aggregate Functions
- MySQL Misc Concepts
- MySQL - NULL Values
- MySQL - Transactions
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - SubQuery
- MySQL - Comments
- MySQL - Check Constraints
- MySQL - Storage Engines
- MySQL - Export Table into CSV File
- MySQL - Import CSV File into Database
- MySQL - UUID
- MySQL - Common Table Expressions
- MySQL - On Delete Cascade
- MySQL - Upsert
- MySQL - Horizontal Partitioning
- MySQL - Vertical Partitioning
- MySQL - Cursor
- MySQL - Stored Functions
- MySQL - Signal
- MySQL - Resignal
- MySQL - Character Set
- MySQL - Collation
- MySQL - Wildcards
- MySQL - Alias
- MySQL - ROLLUP
- MySQL - Today Date
- MySQL - Literals
- MySQL - Stored Procedure
- MySQL - Explain
- MySQL - JSON
- MySQL - Standard Deviation
- MySQL - Find Duplicate Records
- MySQL - Delete Duplicate Records
- MySQL - Select Random Records
- MySQL - Show Processlist
- MySQL - Change Column Type
- MySQL - Reset Auto-Increment
- MySQL - Coalesce() Function
MySQL - UPDATE Statement
MySQL UPDATE Statement
The UPDATE operation on any table updates one or more records, which are already available. You can update the values of existing records in MySQL using the UPDATE statement. To update specific rows, you need to use the WHERE clause along with it.
Syntax
Following is the syntax of the UPDATE statement in MySQL −
UPDATE table_reference SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];
Example
Assume we have created a table in MySQL with name EMPLOYEES as shown below −
CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT );
And if we have inserted 4 records in to it using INSERT statements as −
INSERT INTO EMPLOYEE VALUES ('Krishna', 'Sharma', 19, 'M', 2000), ('Raj', 'Kandukuri', 20, 'M', 7000), ('Ramya', 'Ramapriya', 25, 'F', 5000), ('Mac', 'Mohan', 26, 'M', 2000);
Following MySQL statement increases the age of all male employees by one year −
UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = 'M';
Verification
If you retrieve the contents of the table, you can see the updated values as −
select * from EMPLOYEE;
Output
Following is the output of the above query −
FIRST_NAME | LAST_NAME | AGE | SEX | INCOME |
---|---|---|---|---|
Krishna | Sharma | 20 | M | 2000 |
Raj | Kandukuri | 21 | M | 7000 |
Ramya | Ramapriya | 25 | F | 5000 |
Mac | Mohan | 27 | M | 2000 |
The IGNORE clause
When you use the IGNORE clause along with the UPDATE statement −
- The update occurs though an error occurs during the update.
- If a duplicate conflict occurs in the any of the rows with UNIQUE key constraints that row will not be updated.
Example
Assume we have created a table named data using the CREATE statement shown below−
CREATE TABLE data( ID INT, NAME CHAR(20), AGE INT, SALARY INT );
Now, let's insert some records into the data table −
INSERT INTO data values (101, 'Raja', 25, 55452), (102, 'Roja', 29, 66458), (103, 'Roja', 35, 36944);
Following query updates the salary value of the employee name roja −
UPDATE data set SALARY = SALARY+3000 where NAME = 'Roja';
Verification
If you verify the content of the table, you will see that the salary of "Roja" increases by 3000.
SELECT * FROM data;
Output
The above query produces the following output −
ID | NAME | AGE | SALARY |
---|---|---|---|
102 | Raja | 25 | 55452 |
103 | Roja | 29 | 69458 |
104 | Roja | 35 | 39944 |
If you use the UPDATE query along with the IGNORE clause and execute the above query again, since there are 2 rows with the required name the query will be ignored.
UPDATE IGNORE data set SALARY = SALARY+3000 where NAME = 'Roja';
If you verify the content of the table, you will see that the salary of "Roja" increases by 3000.
Verification
SELECT * FROM data;
Output
Following is the output of the above mysql query −
ID | NAME | AGE | SALARY |
---|---|---|---|
102 | Raja | 25 | 55452 |
103 | Roja | 29 | 72458 |
104 | Roja | 35 | 42944 |
Updating multiple column values
Using the UPDATE statement, you can update values of multiple columns as shown below
UPDATE data set SALARY = SALARY+3000, AGE = AGE+3;
If you verify the content of the table, you will see that the age increases by 3 and salary increases by 3000.
Verification
SELECT * FROM data;
Output
The above mysql query will generate the following output −
ID | NAME | AGE | SALARY |
---|---|---|---|
102 | Raja | 28 | 58452 |
103 | Roja | 32 | 78458 |
104 | Roja | 38 | 48944 |
With the ORDER BY clause
The ORDER BY clause is used to arrange the records of a table based on the specified column we can use this clause along with the UPDATE statement as shown below −
UPDATE table_name ORDER BY column_name;
Where table_name is the name of the table and column_name is the name of the column.
Example
Following query updates the salary values and arranges the records of the table based on salary −
UPDATE data SET SALARY = SALARY + 2000 ORDER BY SALARY DESC;
You can verify the updated values as shown below −
Verification
SELECT * FROM data;
Output
The above query produces the following output −
ID | NAME | AGE | SALARY |
---|---|---|---|
102 | Raja | 28 | 60452 |
103 | Roja | 32 | 80458 |
104 | Roja | 38 | 50944 |
With the LIMIT clause
You can also use the LIMIYT clause along with the UPDATE statement −
UPDATE data SET SALARY = SALARY + 2000 ORDER BY SALARY DESC LIMIT 2;
Since the limit is 2 if you verify the contents of the data table you can observe that only 2 values are updated.
Verification
select * FROM data;
Output
Following is the output of the above query −
ID | NAME | AGE | SALARY |
---|---|---|---|
102 | Raja | 28 | 62452 |
103 | Roja | 32 | 82458 |
104 | Roja | 38 | 50944 |
To Continue Learning Please Login
Login with Google