The DELETE statement only deletes the rows from the table based on the condition defined by WHERE clause or delete all the rows from the table when condition is not specified.
TRUNCATE statement: it is used to delete all the rows from the table and free the containing space.
Difference b/w DROP and TRUNCATE statements
When you use the drop statement it deletes the table's row together with the table's definition so all the relationships of that table with other tables will no longer be valid.
When you drop a table:
- Table structure will be dropped
- Relationship will be dropped
- Integrity constraints will be dropped
- Access privileges will also be dropped
============================================================
Delete all rows
DELETE FROM STUDENT_NAME;
============================================================
Delete one row:
DELETE FROM student_name WHERE id = 003;
============================================================
Delete Duplicate rows:
SELECT DISTINCT column1, column2,....columnN
FROM table _name
WHERE [conditions]
SELECT DISTINCT PERCENTAGE FROM STUDENTS ORDER BY PERCENTAGE;
============================================================
Delete VIEW:
DROP VIEW view_name ;
============================================================
SQL DELETE JOIN
This is very commonly asked question that how to delete or update rows using join clause
It is not a very easy process, sometimes, we need to update or delete records on the basis of complex WHERE clauses.
There are three tables which we use to operate on SQL syntax for DELETE JOIN.
These tables are table1, table2 and target table.
SQL Syntax for delete JOIN
DELETE [target table]
FROM [table1]
INNER JOIN [table2]
ON [table1.[joining column] = [table2].[joining column]
WHERE [condition]
Syntax for update
UPDATE [target table]
SET [target column] = [new value]
FROM [table1]
INNER JOIN [table2]
ON [table1.[joining column] = [table2].[joining column]
WHERE [condition]
