Wednesday, 15 March 2017

SQL DML(Insert,Update,Delete)

SQL INSERT STATEMENT

SQL INSERT statement is a SQL query. It is used to insert a single or a multiple records in a table.
There are two ways to insert data in a table:   
  1. By SQL insert into statement
    1. By specifying column names
    2. Without specifying column names
  2. By SQL insert into select statement

1) Inserting data directly into a table

You can insert a row in the table by using SQL INSERT INTO command. But there are 2 ways to do this.
You can specify or ignore the column names while using INSERT INTO statement.
To insert partial column values, you must have to specify the column names. But if you want to insert all the column values, you can specify or ignore the column names.
If you specify the column names, syntax of the insert into statement will be as follows:
1.    INSERT INTO TABLE_NAME  
2.    [(col1, col2, col3,.... col N)]  
3.    VALUES (value1, value2, value 3, .... Value N);  
Here col1, col2, col3, .... colN are the columns of the table in which you want to insert data.
Note: At the time of inserting a row into table, if you add values for all columns then there is no need to specify the column name in SQL INSERT query. Moreover, you must be sure that you are entering the values in the same order as the columns exist.
But, If you ignore the column names, syntax of the insert into statement will be as follows:
1.    INSERT INTO TABLE_NAME  
2.    VALUES (value1, value2, value 3, .... Value N);  

2) Inserting data through SELECT Statement

SQL INSERT INTO SELECT Syntax
1.    INSERT INTO table_name  
2.    [(column1, column2, .... column)]  
3.    SELECT column1, column2, .... Column N  
4.    FROM table_name [WHERE condition];  
Note: when you add a new row, you should make sure that data type of the value and the column should be matched.
If any integrity constraints are defined for the table, you must follow them.

SQL INSERT INTO VALUE

There are two ways to insert values in a table.
In the first method there is no need to specify the column name where the data will be inserted, you need only their values.
1.    INSERT INTO table_name  
2.    VALUES (value1, value2, value3....);  
The second method specifies both the column name and values which you want to insert.
1.    INSERT INTO table_name (column1, column2, column3....)  

2.    VALUES (value1, value2, value3.....); 


SQL UPDATE

The SQL commands (UPDATE and DELETE) are used to modify the data that is already in the database. The SQL DELETE command uses a WHERE clause.
SQL UPDATE statement is used to change the data of the records held by tables. Which rows is to be update, it is decided by a condition. To specify condition, we use WHERE clause.
The UPDATE statement can be written in following form:
1.    UPDATE table_name SET [column_name1= value1,... column_nameN = value N] [WHERE condition]  
Let's see the Syntax:
1.    UPDATE table_name  
2.    SET column_name = expression  
3.    WHERE conditions  
Let's take an example: here we are going to update an entry in the source table.
SQL statement:
1.    UPDATE students  
2.    SET User_Name = 'beinghuman'  
3.    WHERE Student_Id = '3'  
Source Table:
Student_Id
FirstName
LastName
User_Name
1
Ada
Sharma
sharmili
2
Rahul
Maurya
sofamous
3
James
Walker
jonny
See the result after updating value:
Student_Id
FirstName
LastName
User_Name
1
Ada
Sharma
sharmili
2
Rahul
Maurya
sofamous
3
James
Walker
beinghuman

 

Updating Multiple Fields:

If you are going to update multiple fields, you should separate each field assignment with a comma.
SQL UPDATE statement for multiple fields:
1.    UPDATE students  
2.    SET User_Name = 'beserious', First_Name = 'Johnny'  
3.    WHERE Student_Id = '3'  
Result of the table is given below:
Student_Id
FirstName
LastName
User_Name
1
Ada
Sharma
sharmili
2
Rahul
Maurya
Sofamous
3
Johnny
Walker
beserious
MYSQL SYNTAX FOR UPDATING TABLE:
1.    UPDATE table_name  
2.    SET field1 = new-value1, field2 = new-value2,  
3.    [WHERE CLAUSE]  
SQL UPDATE SELECT:
SQL UPDATE WITH SELECT QUERY:
We can use SELECT statement to update records through UPDATE statement.
SYNTAX:
1.    UPDATE tableDestination  
2.    SET tableDestination.col = value  
3.    WHERE EXISTS (  
4.    SELECT col2.value  
5.    FROM  tblSource  
6.    WHERE tblSource.join_col = tblDestination. Join_col  
7.    AND  tblSource.Constraint = value)  
You can also try this one -
1.    UPDATE   
2.    Table   
3.    SET  
4.    Table.column1 = other table.column 1,  
5.    Table.column2 = other table.column 2  
6.    FROM   
7.    Table  
8.    INNER JOIN  
9.    Other_table  
10. ON  
11. Table.id = other_table.id  
My SQL SYNTAX:
If you want to UPDATE with SELECT in My SQL, you can use this syntax:
Let's take an example having two tables. Here,
First table contains -
Cat_id, cat_name,
And the second table contains -
Rel_cat_id, rel_cat_name
SQL UPDATE COLUMN:
We can update a single or multiple columns in SQL with SQL UPDATE query.
SQL UPDATE EXAMPLE WITH UPDATING SINGLE COLUMN:
1.    UPDATE students  
2.    SET student_id = 001  
3.    WHERE student_name = 'AJEET';  
This SQL UPDATE example would update the student_id to '001' in the student table where student_name is 'AJEET'.
SQL UPDATE EXAMPLE WITH UPDATING MULTIPLE COLUMNS:
To update more than one column with a single update statement:
1.    UPDATE students  
2.    SET student_name = 'AJEET',  
3.    Religion = 'HINDU'  
4.    WHERE student_name = 'RAJU';  
This SQL UPDATE statement will change the student name to 'AJEET' and religion to 'HINDU' where the student name is 'RAJU'.

SQL UPDATE with JOIN

SQL UPDATE JOIN means we will update one table using another table and join condition.

How to use multiple tables in SQL UPDATE statement with JOIN

Let's take two tables, table 1 and table 2
Col 1
Col 2
Col 3
1
1
11
First
2
11
12
Second
3
21
13
Third
4
31
14
Fourth

Col 1
Col 2
Col 3
1
1
21
Two-One
2
11
22
Two-Two
3
21
23
Two-Three
4
31
24
Two-Four
Our requirement is that we have table 2 which has two rows where Col 1 is 21 and 31. We want to update the value from table 2 to table 1 for the rows where Col 1 is 21 and 31.
We want to also update the values of Col 2 and Col 3 only.
The most easiest and common way is to use join clause in the update statement and use multiple tables in the update statement.
1.    UPDATE table 1  
2.    SET Col 2 = t2.Col2,  
3.    Col 3 = t2.Col3  
4.    FROM table1 t1  
5.    INNER JOIN table 2 t2 ON t1.Col1 = t2.col1  
6.    WHERE t1.Col1 IN (21,31)  
Check the content of the table
SELECT FROM table 1
SELECT FROM table 2
Col 1
Col 2
Col 3
1
1
11
First
2
11
12
Second
3
21
23
Two-Three
4
31
24
Two-Four

Col 1
Col 2
Col 3
1
1
21
First
2
11
22
Second
3
21
23
Two-Three
4
31
24
Two-Four
Here we can see that using join clause in update statement. We have merged two tables by the use of join clause.


SQL UPDATE DATE

How to update a date and time field in SQL?
If you want to update a date & time field in SQL, you should use the following query.
let's see the syntax of sql update date.
1.   UPDATE table   
2.   SET Column_Name = 'YYYY-MM-DD HH:MM:SS'  
3.   WHERE Id = value  
Let us check this by an example:
Firstly we take a table in which we want to update date and time fields.
If you want to change the first row which id is 1 then you should write the following syntax:
1.   UPDATE table   
2.   SET EndDate = '2014-03-16 00:00:00.000'   
3.   WHERE Id = 1   
<Note: you should always remember that SQL must attach default 00:00:00.000 automatically.
This query will change the date and time field of the first row in that above assumed table.

SQL DELETE

The SQL DELETE statement is used to delete rows from a table. Generally DELETE statement removes one or more records from a table.

SQL DELETE Syntax

Let's see the Syntax for the SQL DELETE statement:
1.    DELETE FROM table_name [WHERE condition];  
Here table_name is the table which has to be deleted. The WHERE clause in SQL DELETE statement is optional here.

SQL DELETE TABLE

The DELETE statement is used to delete rows from a table. If you want to remove a specific row from a table you should use WHERE condition.
1.    DELETE FROM table_name [WHERE condition];  
But if you do not specify the WHERE condition it will remove all the rows from the table.
1.    DELETE FROM table_name;  

SQL DELETE ROW

Let us take an example of student.
Original table:
ID
STUDENT _NAME
ADDRESS
001
AJEET MAURYA
GHAZIABAD
002
RAJA KHAN
LUCKNOW
003
RAVI MALIK
DELHI
If you want to delete a student with id 003 from the student_name table, then the SQL DELETE query should be like this:
1.    DELETE FROM student_name  
2.    WHERE id = 003;  
Resulting table after SQL DELETE query:


ID
STUDENT_NAME
ADDRESS
001
AJEET MAURYA
GHAZIABAD
002
RAJA KHAN
LUCKNOW


SQL DELETE ALL ROWS

The statement SQL DELETE ALL ROWS is used to delete all rows from the table. If you want to delete all the rows from student table the query would be like,
1.    DELETE FROM STUDENT_NAME;  

SQL DELETE DUPLICATE ROWS

If you have got a situation that you have multiple duplicate records in a table, so at the time of fetching records from the table you should be more careful. You make sure that you are fetching unique records instead of fetching duplicate records.
To overcome with this problem we use DISTINCT keyword.
It is used along with SELECT statement to eliminate all duplicate records and fetching only unique records.
SYNTAX:
The basic syntax to eliminate duplicate records from a table is:
1.    SELECT DISTINCT column1, column2,....columnN  
2.    FROM table _name  
3.    WHERE [conditions]  














Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)