SQL- UPDATE

The UPDATE Statement is used to modify the existing rows in a table.

The Syntax for SQL UPDATE Command is:

UPDATE table_name
SET column_name1 = value1,
column_name2 = value2, ...
[WHERE condition] 

·         table_name - the table name which has to be updated.

·         column_name1, column_name2.. - the columns that gets changed.

·         value1, value2... - are the new values.

NOTE:In the Update statement, WHERE clause identifies the rows that get affected. If you do not include the WHERE clause, column values for all the rows get affected.

For Example: To update the location of an employee, the sql update query would be like,

UPDATE employee
SET location ='Mysore'
WHERE id = 101; 

To change the salaries of all the employees, the query would be,

UPDATE employee
SET salary = salary + (salary * 0.2); 

-------------------------------------------------------------------------------------------

The UPDATE statement allows you to update a single record or multiple records in a table.

The syntax for the UPDATE statement is:

UPDATE table
SET column = expression
WHERE predicates;

Example #1 - Simple example

Let's take a look at a very simple example.

UPDATE suppliers
SET name = 'HP'
WHERE name = 'IBM';

This statement would update all supplier names in the suppliers table from IBM to HP.

Example #2 - More complex example

You can also perform more complicated updates.

You may wish to update records in one table based on values in another table. Since you can't list more than one table in the UPDATE statement, you can use the EXISTS clause.

For example:

UPDATE suppliers
SET supplier_name =	( SELECT customers.name FROM customers WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS   ( SELECT customers.name     FROM customers     WHERE customers.customer_id = suppliers.supplier_id);

Whenever a supplier_id matched a customer_id value, the supplier_name would be overwritten to the customer name from the customers table.