How to make an Insert with columns and values coming from a select?

Asked

Viewed 1,140 times

5

Imagine the following situation: Run a query in a database to acquire a particular record line, and then enter it again in the same table at the end of the records.

It is possible that I can enter the data again in the table without having to mention in the command of INSERT what composes that the registration line?

I mean, I really need to mention that to:

mysql> SELECT * FROM table WHERE t = "12:00:00"

       +----------+------+------+--------- -+
       | t        | val  | lead | lead diff |
       +----------+------+------+-----------+
       | 12:00:00 |  100 |  125 |       -25 |
       +----------+------+------+-----------+

The corresponding code shall be:

mysql> INSERT INTO table (t, val, lead, lead diff) VALUES ("12:00:00", 100, 125, -25);

Or is there a way to make the INSERT plus generalist? (Not to mention everything).

mysql sql
  • 1

    I gave a context in the title of the question. If you consider it inappropriate, you can reverse the revision of the question. ;)

2 answers

7


Yes, it is possible! Enough not inform the clause values replacing it with a query select.

See in practice (without applying the clause where):

> INSERT INTO `insert_select` VALUES ('12:00:00', 100, 125, -25);
> SELECT * FROM `insert_select`;
+----------+------+------+-----------+
| t        | val  | lead | lead_diff |
+----------+------+------+-----------+
| 12:00:00 |  100 |  125 |       -25 |
+----------+------+------+-----------+

----> Aqui é onde sua dúvida é resolvida:
> INSERT INTO `insert_select` (`val`, `lead`) SELECT `val`, `lead` FROM `insert_select`;

> SELECT * FROM `insert_select`;
+----------+------+------+-----------+
| t        | val  | lead | lead_diff |
+----------+------+------+-----------+
| 12:00:00 |  100 |  125 |       -25 |
| NULL     |  100 |  125 |         0 |
+----------+------+------+-----------+

Note that I have specified the columns. But you can also use the joker *:

> INSERT INTO `insert_select` SELECT * FROM `insert_select`;
> SELECT * FROM `insert_select`;
+----------+------+------+-----------+
| t        | val  | lead | lead_diff |
+----------+------+------+-----------+
| 12:00:00 |  100 |  125 |       -25 |
| 12:00:00 |  100 |  125 |       -25 |
+----------+------+------+-----------+

Source: Database Administrators - Insert with Multiple Row Return through select (in English)

And more in the Mysql documentation: 13.2.6.1 INSERT ... SELECT Syntax (in English)

-3

INSERT INTO table SELECT * FROM table WHERE t = "12:00:00";

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.