MySQL Update- Modify Data in a Table

A data once saved in MySQL database tables can be updated. The update may be needed in many situations. You might want to correct a wrongly entered value or update value of a specific column of selected records after a calculation.  The data inserted with MySQL Insert Statement can be updated with MySQL Update statement.

Let us discuss and learn how to update existing records in a Table with MySQL Update DML statement.

Syntax MySQL Update Statement 

UPDATE table-name
SET
Col-name1=new-value1,
Col-name2=new-value2,
..
[WHERE condition]
  • UPDATE and SET- keywords for update statements.
  • table-name- name of the table whose records to be updated
  • col-names- are the names of the columns which have to be given a new value
  • new-values- are the modified values to  replace the old values correspondingly
  • WHERE (optional)- is the optional clause of UPDATE statement. This clause is used to update of values in records on the basis of condition in WHERE clause.

Examples MySQL Update Statements

We will show some examples for updating database table data using MySQL Update statement. Table used in these examples are the once created in MySQL Create Table section.

One column- All rows with same value

MySQL UPDATE statement is used to replace the old values of a column with new values in all the rows of the table. Use this statement if you want to initialize a column with same value. The new value can a value or an expression that calculates or evaluates some value.

Before update

Before Update
UPDATE artists SET band_id=2;

After Update

MySQL Update after

A specific column in selected rows

The “Where clause” of the UPDATE statement is used to set the condition to choose which rows will be updated with MySQL Update statement. Only those rows are updated which satisfy the condition. 

UPDATE artists SET band_id=1 WHERE country='UK';

After Update

With Where

Multiple Columns in All rows

MySQL Update statement can be used to multiple columns in all records. You don’t need to specify the WHERE clause for this. 

UPDATE artists SET ratings=0, country='None', age='99', first_rel_date='2019-01-01';

After Update

MySQL After update multiple columns

Multiple Columns in selected rows

You can update multiple columns in the UPDATE statement by giving Column-name=Value pairs separated by commas and condition in WHERE Clause.

UPDATE artists SET ratings=1, country='USA', age='99', first_album='Not Known' where band_id=2;

After Update

Update multiple columns with WHERE

Points to Remember – MySQL Update Statement

  • To update multiple columns define required number of ‘column-name= new-value’ pairs separated by commas.
  • The old values will be replaced by new values given in Update statement.
  • WHERE Clause can be used to update all/selective column(s) of selected rows.