| ☰ See All Chapters |
Oracle UPDATE Statement
In Oracle, UPDATE statement is used to update the existing records in a table. Condition specified in WHERE clause decides the number of records being updated. After updating records we need to commit using COMMIT statement to permanently save the data. We can use rollback command to undo the update statement changes.
Data Is Case Sensitive in update statement
Do not forget that SQL statements can be in uppercase or lowercase. However, data is always case-sensitive. For example, if you enter data into the database as uppercase, it must be referenced in uppercase.
Syntax to update values
UPDATE <table-name> SET <column1> = <value1>, <column2> = <value2> , <column2> = <value2> [WHERE condition]; COMMIT; |
Example to update values
CREATE TABLE EMPLOYEE(EMPLOYEE_ID NUMBER(5), NAME VARCHAR2(30), SALARY NUMBER (6));
INSERT INTO EMPLOYEE VALUES(101,'Manu',1000);
INSERT INTO EMPLOYEE VALUES(102,'Advith',2000);
COMMIT;
---------Update all records------------- UPDATE EMPLOYEE SET SALARY=2222;
COMMIT;
---------Update selected records-------------
UPDATE EMPLOYEE SET SALARY = 3333 WHERE EMPLOYEE_ID = 101;
UPDATE EMPLOYEE SET SALARY = 4444 WHERE NAME = 'Advith';
COMMIT;
|
Updating by the value from another table
We can update the value from another table by using select query in place of value. Select query used should have single column and return a single value. In the below example we are setting the Salary of an employee from the CUSTOMER table.
UPDATE EMPLOYEE SET SALARY = (SELECT SALARY FROM CUSTOMER WHERE EMPLOYEE_ID = 101 ) WHERE EMPLOYEE_ID = 101; |
All Chapters
