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
Advertisements