Here we will discuss some useful concepts on insert statement and limits, which will be proved to be a boon to your development career if you don’t know these yet.
Let’s name them first
- On duplicate update or insert update.
- Insert into select.
- Insert Ignore.
- Use of Limit.
How to use On Duplicate Update Statement?
If you want to insert in the table and on the conflict of the primary key or unique index it will update the conflicting row instead of inserting that row.
Syntax : insert into table1 set column1 = a, column2 = b on dulplicate update column2 = c;
Now here, this insert statement may look different from what you have seen earlier. This insert statement trying to insert a row in table1 with the value of a and b into column column1 and column2 respectively.
Let’s understand this statement into deep:
For example:- here column1 is defined as the primary key in table1.
Now if in table1 there is no row having the value “a” in column1. So this statement will insert a row in the table1.
Now if in table1 there is a row having the value “a” in column2. So this statement will update the row’s column2 value with “c” where the column1 value is “a”.
So if you want to insert a new row otherwise update that row on the conflict of the primary key or unique index.
How to use Insert into select Statement?
If you want to insert into a table from the selection of rows using some condition from another. It will insert one or more row at a time based on the selected rows.
Syntax: insert into table2 (column1, column2, column3) select column1, column2, column3 from table1 where condition;
This statement will be very useful if want inserts some calculated data in the table based on the value of data from another table or you want backup a data from one table into some other.
How to use Insert Ignore Statement?
In MySQL Insert ignore statement is another kind of insert statement in which while inserting a new row if there is an old row having the same primary key or unique index as the new row then it will delete the old rows data and insert new row data.
Insert ignore statement is same as the insert statement except in place of “insert” we write “insert ignore”.
Syntax: insert ignore into table2 (column1, column2, column3) values (value1, value2,value3); insert ignore into table2 values (value1, value2,value3); insert ignore into table2 set column1 = value1, column2 = value2, column3 = value3; insert into table2 (column1, column2, column3) select column1, column2, column3 from table1 where condition;
This statement can also be used as an alternative to “on duplicate update”.
How to use Limit in Statement?
In MySQL, Limit is used to show the specific number of rows while using select statement in MySQL.
Syntax: select [column1,column2,column3] from table1 where [condition] limit 10.
This statement will show only 10 rows from the top. For example, If a select statement using a certain condition giving you 1000 or more records you want only 10. Then this requirement can be fulfilled using Limit.
There is one more way to use Limit
Syntax: select [column1,column2,column3] from table1 where [condition] limit 11,10.
Now, this statement will again show 10 records but not from the top, actually, it will show you records from 11th to 20th row from the select statement. For example, If a select statement using a certain condition giving you 1000 or more records. Now, you want records from 11th to 20th then you need to use limit as mentioned above. This will be very useful if you want to show data of 1000 records in the chunk of 10.
read some more concepts on MySQL on link