Alter, Modify & Update table in Oracle

How to alter, update or modify table?
 

Let’s create a table to understand alter table concept, update table concept and modify table concept.

CREATE TABLE EMPLOYEE (EMPNO NUMBER, EMPNAME VARCHAR2 (10), SALARY NUMBER (5, 2));

INSERT INTO EMPLOYEE (EMPNO, EMPNAME) VALUES (1,’CHANCHAL’);

INSERT INTO EMPLOYEE (EMPNO, EMPNAME) VALUES (2,’WANKHADE’);

SELECT * FROM EMPLOYEE;

What is altering table and why it is used?

Alter table allows you to add one or more columns or add any constraint if you have missed out while creating a table. For example, if I have missed the column ADDRESS in the EMPLOYEE table and I want to add it in a table. Look at the syntax while adding two columns:-

If I want to add constraint in EMPLOYEE table then I can add this by following syntax:-

What is updating a table?

Update statement will allow you to update any existing value. In EMPLOYEE table there are SALARY, PHONE_NO, DEPT_NO and ADDRESS columns empty. Let’s try to update them to fill some value.

Make sure you have committed the data to save it permanently otherwise it will not be saved in your table.

What is modifying a table?

 Modify can be used to modify data type of the existing tables columns. Suppose if you are required to modify DEPT_NO column to VARCHAR2 data type of EMPLOYEE table.

Remember while modifying data type of the column; you need to make sure that the column is empty in case you are trying to switch from number to char or varchar2. In case you have to increase the length of the varchar2 data type then you can do it easily but you can’t modify data type from bigger length to smaller length. See the example above. I have created table called

DEPARTMENT which is having DEPT_NO and DEPT_TYPE columns. Initially I have given DEPT_NO column number data type. After creating table I have modified that to varchar2 data type having length 10 and inserted some values into the DEPARTMENT table. After that I have changed the length of the DEPT_NO column to varchar2 with length 15 and inserted 15 length values. Now I want to again modify the DEPT_NO column and make the length 14. I tried to perform that but it does not allow me to decrease the length of the column data type while there is a value already present which is having  more than the length I want to modify.

In short you cannot directly switch to another data type without empting column first and you cannot modify less length of data type while there is a value having more length than you want to modify.

ng" | � t e x�Y ��W v:shape>

Type username (schema name), password and host string:-

Click OK.

Oracle sql developer:-

Install oracle sql developer or click on sql developer.exe file, it will show you initialization process:-

Click on

Once you click on  you will land onto the below screen. Type the name of the connection as you want, type the username, password, and then click on save password checkbox, type host name or ip_address of the machine, specify the port number and database SID or Service name, after putting all the information click on test  or you can directly click on connect button:-

Once you click on save button, the connection will be saved in the connection tab so you can use it again and again. You can see below the name CONNECTION_TO_SCOTT is present in the connection tab.

If you want to add more connection then just click on  and put all the required information. After you saved it, it will be listed in connection tab.

Read Also:-