SQL -Structured Query Language - Sql Update - Update Record Tutorial

To update only the record of the existing created table with or without a special condition. Then we can use an ' UPDATE ' statement.

• To use it for a single column

Syntax-

The basic syntax of the UPDATE statement is given below –

UPDATE table_name SET colname = colname_value WHERE [CONDITION];

For example-

UPDATE regions SET region_name = 'FresherBell India' WHERE region_id = '1';

Run Query

In this statement, it will set the region_name to 'FresherBell India' of table regions, whose region_id is 1.

This Statement is only used for a single record.

In Mysql Workbench-

In this statement, it will set the name to 'FresherBell Tutorial' of a table user_detail, whose id is 1.

This Statement is only used for a single record.

• To use it for multiple columns, we have to use a comma-separated with Square Bracket.

Syntax-

The basic syntax of the ‘UPDATE‘ statement for updating multiple columns at once is given below –

UPDATE table_name 
SET 
colname1 = colname1_value,colname2 = colname2_value,
…,’colnameN = colnameN_value
WHERE [CONDITION];

For example-

UPDATE departments 
SET 
department_name = 'FresherBell India', 
manager_id = 425, 
location_id = 2000 
WHERE department_id = '20';

Run Query

In this statement, it will set the department_name to ‘FresherBell India’, manager_id to 425, and location_id to 2000 of table departments, whose departmnet_id is 20.

This Statement is only used for multiple columns.

In Mysql Workbench-

In this statement, it will set the name to ‘FresherBell Tutorial’, email to ‘fresherbellteam@support.com’, and dateofbirth to 2020-09-12 of a table user_detail, whose id is 2.

• But if we do not include the where condition in the statement. Then it will update all the records of the user_detail table. (Not Recommended, Just for knowledge)

Syntax-

UPDATE table_name 
SET 
colname1 = colname1_value,colname2 = colname2_value,
…,’colnameN = colnameN_value;

For example-

UPDATE departments 
SET 
department_name = 'FresherBell India', 
manager_id = 425, 
location_id = 2000;

Run Query

In Mysql Workbench-

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

It will give an 1175 error because Mysql Workbench is in safe mode (which does not allow to update or delete table without WHERE condition, because it is unsafe ). So for this disable safe mode. (Not recommended, just for knowledge)