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.

SQL query to UPDATE table in the DATABASE

The UPDATE Statement

When we need to modify existing row, we need to use the UPDATE statement. If needed, we can update multiple records at the same time. For the UPDATE to be successful the user must have data manipulation privileges (UPDATE privilege) on the table or column and the updated value must not conflict with all the applicable constraints (such as primary keys, unique indexes, CHECK constraints, and NOT NULL constraints).

Syntax

UPDATE table_name

SET column_name = value, column_name = value...

WHERE condition;

table_name	It is the table name
SET	Modification clause
column_name	Existing column name which is to be modified.
value	New value to be updated to the column or subquery for the column.
condition	Basis on which the modification is performed. It can include column names, expressions, constants, subqueries, and comparison operators

Example

Let us see the output row from students table where the rollno is 4.

select * from students

where rollno=4;

We will update the lastname for the student whose roll no is 4 with use of update statement example as shown below.

update students

set lastname='Green'

where rollno=4;

commit;

select * from students

where rollno=4;

We can see that the Oliver’s lastname has been updated from Queen to Green.

We must prefer the primary key to identify a single row. Using other columns may unexpectedly cause several rows to be updated. If we omit the WHERE clause, ALL records will be updated.

update students

set lastname='Green';

commit;

The above query will update all the rows in students table and set lastname to Green.

select * from students;

So the UPDATE statement should be used with caution.

Updating Multiple Columns with a Subquery

It is possible to update multiple columns in the single query. Also there is provision to take the value which has to be set as an output from the sub query. A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select. The SELECT query of a subquery is always enclosed in parentheses. 

Syntax

UPDATE table_name

SET column_name = (SELECT column_name FROM table_name WHERE condition),

      column_name = (SELECT column_name FROM table_name WHERE condition)

WHERE condition;

Example

Let us update students table using subquery on the same students table as shown below. Here we are updating firstname and city column for row with roll no 45.

update students

set lastname=(select firstname from students where rollno=14),

      CITY=(select CITY from students where rollno=21)

where rollno=45;

commit;

The lastname column in the outer query is populated by subquery which refers to firstname column of students where roll no is 14.

The city column the outer query is populated by subquery which refers to city column of students where roll no is 21.

Before update the output of students table is as shown below.

select * from students;

After update the output of students table is as shown below.

select * from students;

Updating Rows Based on another Table

We can update multiple columns as well as from multiple sub queries and also refer to multiple tables.

Syntax

UPDATE table_name

SET column_name = (SELECT column_name FROM table_name_1 WHERE condition),

      column_name = (SELECT column_name FROM table_name_2 WHERE condition)

WHERE condition;

Example

Now we will update students_copy table by taking values  from students table.

select * from students_copy; 

Below is an update statement where the lastname for Barry is Allen is updated to Kara. The lastname column in the outer query is populated by subquery which refers to firstname column of students where roll no is 14.

update students_copy

set lastname=(select firstname from students where rollno=14)

where rollno=45;

commit;

Let us check the output after update.

select * from students_copy;