ALTER TABLE SQL QUERY

The ALTER TABLE Statement

Once the table is created, and sometimes we need to change the table structure or column properties need to be changed, or we need to remove certain columns. We need to use ALTER TABLE statement. Use the ALTER TABLE statement to perform operations like adding a new column, modify an existing column, defining a default value for the new column and drop a column.

Syntax

ALTER TABLE table_name

ADD (column datatype [DEFAULT expr], column datatype...);

ALTER TABLE table_name

MODIFY (column_name datatype [DEFAULT expr], column datatype...);

ALTER TABLE table_name

DROP (column column_name);

table_name	It is the table name
ADD|MODIFY|DROP	Modification clause
Column_name	New column name which is to be added. Existing column name which is to be modified or dropped.
datatype	I s the data type and length of the new column
DEFAULT expr	The default value for a new column

ADD
ALTER TABLE table_name

ADD (column datatype [DEFAULT expr], column datatype...);

Example
Let us now add Country column to students table.
alter table students

add Country varchar(05);

select * from students;

The new column becomes the last column. The new column is initially NULL for all the rows. We will try updating table for this column.

update students set country='NEWYORK'

where CITY='Central';

Above error occurred since the width defined was 05 and we tried to update with value of 7. This is one of the scenario in which we require modification of column data type length which is done with use of MODIFY clause as shown below syntax.

MODIFY

ALTER TABLE table

MODIFY (column datatype [DEFAULT expr], column datatype...);

We can increase the precision of a numeric column using this clause. Also can increase the size of numeric or character columns. We can decrease the width of a column only if the column contains only null values or if the table is empty i.e. has zero rows. We are restricted to change the data type only if the column contains null values. Also perform data type conversion such as converting a CHAR to the VARCHAR2 data type or a VARCHAR2 to the CHAR only if the column contains null values or if you do not change the size.

Example

Modifying VARCHAR value from 05 to 50 for column Country in students table.

alter table students

modify Country varchar(50);

update students set country='NEWYORK'

where CITY='Central';

commit;

select * from students;

DROP

ALTER TABLE table

DROP (column);

When we don’t need some columns in our table we can use  the DROP COLUMN clause to drop columns. It is not mandatory to have data in to drop a column. The table must have at least one column remaining in it after it is altered.  Only one column can be dropped at a time. Once a column is dropped, it cannot be recovered.

Example

Removing the Country column from students table.

alter table students

drop column Country;

select * from students;

SET UNUSED

The SET UNUSED option is use to mark one or more columns as unused.

DROP UNUSED COLUMNS option is used to remove the columns that are marked as unused.

ALTER TABLE table

SET UNUSED (column_name);

OR

ALTER TABLE table

SET UNUSED COLUMN column_name;

ALTER TABLE table

DROP UNUSED COLUMNS;

When a column is dropped from a table, it will also drop any other columns in that table that are marked with the SET UNUSED option. The SET UNUSED option marks one or more columns as unused so that they can be dropped on demand when a system resource is lower. Setting a column as UNUSED just marks a column for logical deletion and the columns are no longer available for DML, DDL or SELECT commands. But the data in the columns is still intact in the segment and not dropped. UNUSED is much faster and as it doesn't update the data file with the changes. The actual DROP will take place after issuing DROP UNUSED columns.

Therefore, if the intent  is to cut-off access to columns without I/O latency in production during peak business hours, then first mark it as UNUSED and then during the weekend or off peak hours, complete the physical drop of the column using DROP UNUSED.

TRUNCATE DROP AND DELETE SQL Statement

TRUNCATE SQL Statement

TRUNCATE is a DDL command. It performs the same function as a DELETE statement without a WHERE clause. TRUNCATE is executed using a table lock and whole table is locked for remove all records. This statement mostly used to delete the table data permanently. Truncate statement deletes the storage occupied by the table. The deleted storage can be used by the table in future. We cannot use Where clause with TRUNCATE. The truncate statement can’t be rolled back also we can’t fire any trigger. To truncate a table, the table must be in our schema or we must have DROP ANY TABLE system privilege. Truncate statement is faster and does not use undo as delete statement. In truncate there is minimal logging in transaction log, so it is performance wise faster. De-allocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter. Also Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process. To use Truncate on a table you need at least ALTER permission on the table. Truncate uses the less transaction space than Delete statement. But truncate cannot be used with indexed views.

Syntax

TRUNCATE TABLE TABLE_NAME;

Example

Let us have a look at below STUDENTS_COPY table.

SELECT * FROM STUDENTS_COPY;

TRUNCATE TABLE STUDENTS_COPY;

SELECT * FROM STUDENTS_COPY;

Now the all the records of STUDENTS_COPY table are removed, but table structure is present.

Preserving Materialized View Logs After Truncate

TRUNCATE TABLE TABLE_NAME PRESERVE MATERIALIZED VIEW LOG;

DELETE SQL Statement

DELETE is a DML command. DELETE is executed using a row lock, each row in the table is locked for deletion. We can use where clause with DELETE to filter & delete specific records. It maintains the log, so it is slower than TRUNCATE. Delete uses the more transaction space than TRUNCATE statement. To use Delete you need DELETE permission on the table. It can be used with indexed views.

Syntax

DELETE FROM TABLE_NAME

[WHERE CONDITION];

Have a look at our previous post on DELETE SQL query. TRUNCATE is DDL statement which implicitly commits. If you are not sure of the action you do in a schema, and want to commit only after a manual verification, then do not TRUNCATE, use DELETE instead.

DROP SQL Statement

DROP is a DDL command. The DROP TABLE statement is used to remove a table definition and all the data, indexes, triggers, constraints, permission specifications for that table and indexes of related objects.Drop statement is an irreversible statement. the operation cannot be rolled back. When we execute the DROP statement then no DML triggers will be fired.

Syntax

DROP TABLE TABLE_NAME;

Example

Let us DROP STUDENTS_COPY by using DROP statement.

DROP TABLE STUDENTS_COPY;

SELECT * FROM STUDENTS_COPY;

Table does not exist after drop statement.

DELETE a row from table SQL query

The SQL DELETE STATEMENT

The DELETE statement is used to delete existing records in a table. The DELETE statement does not return any rows; that is, it will not generate a result set. Executing a DELETE statement can cause triggers to run that can cause deletes in other tables. For example, if two tables are linked by a foreign key and rows in the referenced table are deleted, then it is common that rows in the referencing table would also have to be deleted to maintain referential integrity. We can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.

Syntax

DELETE FROM TABLE_NAME

[WHERE CONDITION];

Example

Let us see all the records of STUDENTS_COPY table first.

SELECT * FROM STUDENTS_COPY;

The following SQL statement deletes the student   "Oliver Queen" from the STUDENTS_COPY  table.

DELETE FROM STUDENTS_COPY

WHERE ROLLNO=4;

COMMIT;

We can see below that the record for the Oliver Queen is not present in the table.

SELECT * FROM STUDENTS_COPY;

Example

We can delete all rows in a table without deleting the table, which means that the table structure, attributes, and indexes will be not altered.

DELETE FROM STUDENTS_COPY;

COMMIT;

SELECT * FROM STUDENTS_COPY;

Example using EXIST clause

When we want to delete records in one table based on values in another table. Since we cannot list more than one table in the FROM clause while performing a delete, there we can use the EXIST clause.

Now we will delete record from STUDENTS_COPY table based on condition met on MARKS table.

SELECT * FROM MARKS;

SELECT * FROM STUDENTS_COPY;

DELETE FROM STUDENTS_COPY

WHERE EXISTS

  (SELECT *

   FROM MARKS

   WHERE MARKS.ROLLNO = STUDENTS_COPY.ROLLNO

   AND MARKS.ROLLNO =12);

SELECT * FROM STUDENTS_COPY;

We can see that the record for "Leonard Snart" is removed from the STUDENTS_COPY table based on ROLLNO.  mentioned in where clause on MARKS table.