Insert Multiple Rows with 1 INSERT Statement in MySQL

Q

How To Insert Multiple Rows with One INSERT Statement in MySQL?

✍: FYIcenter.com

A

If you want to insert multiple rows with a single INSERT statement, you can use a subquery instead of the VALUES clause. Rows returned from the subquery will be inserted the target table. The following tutorial exercise gives a good example:

mysql> INSERT INTO fyi_links SELECT id+500, REVERSE(url), 
   notes, counts, created FROM fyi_links;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT id, url, notes, counts, DATE(created) 
   FROM fyi_links;
+-----+-------------------+-------+--------+---------------+
| id  | url               | notes | counts | DATE(created) |
+-----+-------------------+-------+--------+---------------+
| 101 | dev.fyicenter.com | NULL  |      0 | 2006-04-30    |
| 102 | dba.fyicenter.com | NULL  |      0 | 2006-08-31    |
| 103 | sqa.fyicenter.com | NULL  |   NULL | 2006-08-31    |
| 110 |                   | NULL  |   NULL | 2006-08-31    |
|   0 | www.fyicenter.com | NULL  |   NULL | 2006-08-31    |
| 601 | moc.retneciyf.ved | NULL  |      0 | 2006-04-30    |
| 602 | moc.retneciyf.abd | NULL  |      0 | 2006-08-31    |
| 603 | moc.retneciyf.aqs | NULL  |   NULL | 2006-08-31    |
| 610 |                   | NULL  |   NULL | 2006-08-31    |
| 500 | moc.retneciyf.www | NULL  |   NULL | 2006-08-31    |
+-----+-------------------+-------+--------+---------------+
10 rows in set (0.00 sec)

⇒ Updating Values in a Table in MySQL

⇐ Violation of Unique Value Constraint in MySQL

⇑ INSERT, UPDATE and DELETE Statements in MySQL

⇑⇑ MySQL Database Tutorials

2018-01-16, 3004🔥, 0💬

UPDATE Using Data from Other Tables in MySQL
How To Use Values from Other Tables in UPDATE Statements in MySQL? If you want to update values in one table with values from another table, you can use a subquery as an expression in the SET clause. The subquery should return only one row for each row in the update table that matches the WHERE clau... 2018-01-13, 3770🔥, 0💬

Using Column Default Values in MySQL
How To Specify Default Values in INSERT Statement in MySQL? If a column is defined with a default value in a table, you can use the key word DEFAULT in the INSERT statement to take the default value for that column. The following tutorial exercise gives a good example: mysql> INSERT INTO fyi_... 2018-01-16, 3275🔥, 0💬

Order of Columns in the SET Clause in MySQL
Is the Order of Columns in the SET Clause Important in MySQL? Yes. The order of columns in the SET clause of the UPDATE statement is important. There is a BIG DIFFERENCE between MySQL and Oracle on update columns with previous values: Oracle provides you the existing values from the database on colu... 2018-01-13, 3245🔥, 0💬

Creating a Test Table in MySQL
How To Create a Testing Table in MySQL? If you want to practice DML statements, like INSERT, UPDATE and DELETE, you should create a testing table. The tutorial exercise shows you a good example: mysql> CREATE TABLE fyi_links (id INTEGER PRIMARY KEY, url VARCHAR(80) NOT NULL, notes VARCHAR(102... 2018-01-24, 3222🔥, 0💬

Use Existing Column Values in the SET Clause in MySQL
How To Use Existing Column Values in the SET Clause in MySQL? If a row matches the WHERE clause in a UPDATE statement, existing values in this row can be used in expressions to provide new values in the SET clause. Existing values are represented by column names in the expressions. The tutorial exer... 2018-01-13, 3182🔥, 0💬

Update Column Values on Multiple Rows in MySQL
How To Update Column Values on Multiple Rows in MySQL? If the WHERE clause in an UPDATE matches multiple rows, the SET clause will be applied to all matched rows. This rule allows you to update values on multiple rows in a single UPDATE statement. Here is a good example: mysql> UPDATE fyi_lin... 2018-01-13, 3137🔥, 0💬

Inserting a New Row into a Table in MySQL
How To Insert a New Row into a Table in MySQL? To insert a new row into a table, you should use the INSERT INTO statement with values specified for all columns as shown in the following example: mysql> INSERT INTO fyi_links VALUES (101, 'dev.fyicenter.com', NULL, 0, '2006-04-30'); Query OK, 1... 2018-01-16, 3115🔥, 0💬

Updating Values in a Table in MySQL
How To Update Values in a Table in MySQL? If you want to update some values in one row or multiple rows in a table, you can use the UPDATE statement. The tutorial script below shows a good example: mysql> UPDATE fyi_links SET counts = 999, notes = 'Good.' WHERE id = 101; Query OK, 1 row affec... 2018-01-13, 3043🔥, 0💬

Violation of Unique Value Constraint in MySQL
What Happens If Unique Value Constraints Are Violated in MySQL? If you are inserting a new record that has values violating a unique constraint, you will get an error. Note that primary key column has a unique value constraint by default. The following tutorial exercise gives you some good examples:... 2018-01-16, 3021🔥, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.