UPDATE Command
• Update command allows us to change the data
present in one or more rows of a table. Updation
can happen based on condition using WHERE clause
also if required. It is a type Data manipulation
command. Syntax:
UPDATE table SET column1 = value1, column2 =
value2, ... WHERE condition
• UPDATE EMP SET SAL = SAL * 1.10;
the above will change the column SAL for all rows as
SAL*1.10
• UPDATE EMP SET COMM=NULL WHERE COMM=0
will update column COMM as NULL if it is 0
UPDATE Command
• UPDATE EMP SET COMM=1200 WHERE COMM IS NULL
The above command will update COMM to 1200 if
COMM is NULL
• UPDATE EMP SET COMM=1000, MGR=8640 WHERE
ENAME="AMIR“
Updating more than one column
• If an attempt is made to update a record with a
value that is tied to an integrity constraint, an error
is returned.
• UPDATE EMP SET EMPNO=8698 WHERE ENAME="AMIR"
Will display an error because EMPNO is primary key
of the table. Entry can’t be duplicated.
DELETE Command
• DELETE command removes one or more rows from
a table. Removal can be done based on condition
also. Syntax
• DELETE FROM table WHERE condition;
To delete all rows:- DELETE FROM EMP
To delete based on a condition:-
• DELETE FROM EMP WHERE JOB IS NULL
ALTER TABLE
• ALTER TABLE – is a DDL command. So far we have
discussed only one DDL command which is CREATE
TABLE.
• ALTER TABLE allows us to change/alter the
structure of the table. Which may be
- adding a column
- changing /redefining a column
- adding/changing integrity constraint
ALTER TABLE
Adding a column
• ALTER TABLE STUDENT ADD (ADMNO INT(11))
- A Column/field names ADMNO is added of the
type INT with the length of 11 digits. More than
one column also can be added. Constraint(s) may
be mentioned.
Redefining a column
• ALTER TABLE STUDENT MODIFY SNAME VARCHAR(40)
- A column SNAME length has been modified as 40.
Dropping a column
• ALTER TABLE STUDENT DROP ADMNO
ALTER TABLE
Adding/changing integrity constraint
• ALTER TABLE STUDENT DROP PRIMARY KEY
will drop the primary key constraint from the table
STUDENT. There won’t be any primary key
constraint in the table STUDENT.
To set Primary Key again:
• ALTER TABLE STUDENT ADD PRIMARY KEY
(ROLLNUMBER)
DROP TABLE
• The DROP TABLE statement removes the
definition of an MySQL table. The database
loses all the data in the table and all other
objects associated with it. It is a DDL command
• Syntax
• DROP TABLE table;
DROP TABLE STUDENT
Note: DDL commands to be written using the
key word TABLE apart from the command.