x

SQL – UPDATE

Prev     Next

SQL – UPDATE statement:

The UPDATE statement in SQL is used to update/modify existing records/values in a table. Either we can update all existing records for all columns in a table or can update only particular columns and particular records in a table using the UPDATE statement in SQL. Syntax of SQL UPDATE statement is given below.


SQL Syntax for UPDATE statement:

Syntax to UPDATE values for all records UPDATE table_name
SET column1=value1, column2=value2, etc
 

Syntax to UPDATE values only to selected records

UPDATE table_name
SET column1=value1, column2=value2, etc
WHERE column_name=value;

Example1 for how to use SQL UPDATE statement:

Please consider the following table with few records as given below.

Table name (for example): student
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows

sql-table1

  • To update a record, i.e. Values for all columns in this table, please execute below query. This query will update values to all records available in this table.

SQL query:

UPDATE student
SET Student_ID = 5, Student_name = ‘Peter’, City = ‘New York’, Age = 30);

SQL query Output:

sql-table7

Note: Please note that all records in the table were updated with new values. If you want to update only selected records, you need to use “where” clause as given below.


Example2 for how to use SQL UPDATE statement:

Please consider our following same table with few records as given below.

Table name (for example): student
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows

sql-table1

  • To update a record, i.e. Values for a particular column and a particular record in this table, please execute below query. This query will update values only in particular records available in this table that we select in “where” clause.

SQL query:

UPDATE student
SET Student_ID = 5, Student_name = ‘Peter’, City = ‘New York’, Age = 30)
where City = ‘Pune’;

SQL query Output:

sql-table8

Note

  • Only one record was selected by the above UPDATE query WHERE the City name was ‘Pune’ and the values were updated what we mentioned in this query. So, we can update any columns and any rows based on the “WHERE” clause.
  • Also, we can use OR, AND operators in WHERE clause to select and then update records/values in a table. Please refer this complete SQL tutorial to know more on these keyword and operators, WHERE clause, OR, AND etc.

Do you know in SQL?

  • It’s a best practice to run select query with WHERE clause to know how many records will get updated by our update query. After that, we can execute our update query.
  • Mistakenly updated many records or with wrong values? Don’t worry. There is a command called ROLLBACK; Please go through this SQL tutorial on SQL – ROLLBACK topic to know more.

Prev     Next



Like it? Please Spread the word!