SQL - UPDATE, DELETE, ALTER STATMENTS

UPDATE Statement:

The UPDATE statement is used to update existing records in a table.

Syntax:

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

   

·       You can use WHERE clause with UPDATE query to update selected rows otherwise all the rows would be affected.

Example:

Consider the CUSTOMERS table having the following records:

Following is an example, which would update ADDRESS for a customer whose ID is 555:

UPDATE CUSTOMER

SET city = 'Pune'

WHERE customerID = 555;

select * from customer;

If you want to modify all CITY and AGE column values in CUSTOMERS table, you do not need to use WHERE clause and UPDATE query would be as follows:

UPDATE CUSTOMER

SET CITY = 'Pune', AGE = 60;

SELECT * FROM CUSTOMER;

 DELETE Statement:

·         The DELETE statement is used to delete rows in a table.

·         The SQL DELETE Query is used to delete the existing records from a table.

·         You can use WHERE clause with DELETE query to delete selected rows, otherwise all the records would be deleted.

Syntax:

DELETE FROM table_name

WHERE [condition];

DELETE FROM table_name
WHERE some_column=some_value;

Following is an example, which would DELETE a customer, whose ID is 54321:

DELETE FROM orders

WHERE id=54321;

If you want to DELETE all the records from CUSTOMERS table, you do not need to use WHERE clause and DELETE query would be as follows:

DELETE FROM CUSTOMERS;

Now, CUSTOMERS table would not have any record.

ALTER Statement:

ALTER TABLE Command:

You would also use ALTER TABLE command to add and drop various constraints on a an existing table.

Syntax:

The basic syntax of ALTER TABLE to add a new column in an existing table is as follows:

ALTER TABLE table_name ADD column_name datatype;

The basic syntax of ALTER TABLE to DROP COLUMN in an existing table is as follows:

ALTER TABLE table_name DROP COLUMN column_name;

The basic syntax of ALTER TABLE to change the DATA TYPE of a column in a table is as follows:

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

The basic syntax of ALTER TABLE to add a NOT NULL constraint to a column in a table is as follows:

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows:

ALTER TABLE table_name

ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

The basic syntax of ALTER TABLE to ADD CHECK CONSTRAINT to a table is as follows:

ALTER TABLE table_name

ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

The basic syntax of ALTER TABLE to ADD PRIMARY KEY constraint to a table is as follows:

ALTER TABLE table_name

ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

The basic syntax of ALTER TABLE to DROP CONSTRAINT from a table is as follows:

ALTER TABLE table_name

DROP CONSTRAINT MyUniqueConstraint;

If you're using MySQL, the code is as follows:

ALTER TABLE table_name

DROP INDEX MyUniqueConstraint;

The basic syntax of ALTER TABLE to DROP PRIMARY KEY constraint from a table is as follows:

ALTER TABLE table_name

DROP CONSTRAINT MyPrimaryKey;

Select * from orders;

ALTER TABLE orders RENAME COLUMN id to OrderId ;

Add the new column:

ALTER TABLE customer ADD age integer;