MySQL Insert statement

Provide syntax with an example for the followings:

1. Insert a single complete row
2. Insert a single partial row
3. Insert multiple rows
4. Insert the result of a query

1. Insert a single complete row
INSERT INTO "table_name" ("column1", "column2", ...) VALUES ("value1", "value2", ...)

Example:
Insert into employee (emp_id, firstname, lastname) values(‘1’,’John’,’player’)

2. Insert a single partial row

Example:
Insert into employee(emp_id, firstname, lastname) values (‘2’, NULL, ‘perry’);

3. Insert multiple rows

Example:
Insert into employee(emp_id, firstname, lastname) values (‘2’, ‘john’ ‘perry’), (‘3’, ‘mac’,’welt’);

4. Insert the result of a query

Example:
INSERT INTO new_employee
(
emp_id,
emp_name,
emp_salary
)
SELECT emp_id,
emp_name,
emp_salary
FROM old_employee;

State syntax of Inserting data into tables with an example.

Insert : Insert statement is used to insert data (row) in a table.

Syntax:
Insert into table_name
Values (value1, 2 ..)

Example:
Insert into customer values (1,’steve’,’james’);

Explain the DELAYED option for the INSERT statement with an example.

The data to be inserted in a table can be delayed until is free from reads.

Syntax:
INSERT DELAYED INTO table (col) VALUES ('val');

The above statement when executed will store the data in memory queue until the table is free from being read. Its most useful when the client cannot wait for the insert to be completed. INSERT DEALYED is not supported for portioned tables or views.

Explain the ON DUPLICATE KEY UPDATE option for the INSERT statement with an example.

If the INSERT statement has ON DUPLICATE KEY UPDATE specified; it will cause a duplicate value on the primary key and an update on the old row.

Example:
INSERT INTO table (x,y,z) VALUES (1,2,3) ON DUPLICATE KEY UPDATE z=z+1;

Here, one row is affected if the row is inserted as a new record and 2 rows are affected if the row inserted is updating existing record.
The above statement can be interpreted as
UPDATE table SET z=z+1 where a=1;
MySQL manipulating data
MySQL manipulating data - guidelines for updating and deleting data
MySQL views
MySQL views - Why use views. View rules and restrictions
MySQL stored procedures
MySQL stored procedures - What are stored procedures? Explain the use of stored procedures.
Post your comment