Menu
I have two MySQL tables a and b with fields x and y. Table b has 1 extra field z. Table a is in database db1 and b is in db2. I want to copy x and y from a to b and set a static value for z. How can I do that ?
db1.a.x -> db2.b.x db1.a.y -> db2.b.y 4 -> db2.b.z So far I have:
"INSERT INTO db2.b (x,y) SELECT x,y FROM db1.a" How do I set db2.b.z to 4 ? I do not want to set a permanent default variable for the table.
687
Selecting Static Values Static values can be inserted into a resultset returned from a SELECT query as another column. Simply use the static value as a column to select, and the query will return a column where the name of the column is the static value, and every row in that column will return that same static value.
The following is the basic syntax that illustrates the use of the INSERT INTO SELECT command in MySQL. If we want to copy all data from one table into another table, we can use the below statement: INSERT INTO table_name2. SELECT * FROM table_name1.
You can use a select-statement within an INSERT statement to insert zero, one, or more rows into a table from the result table of the select-statement. The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data.
You can still insert records into the destination table with specifying column names in the INSERT INTO SELECT statement.
SELECT 4 will give you 4, so try:
INSERT INTO db2.b (x,y,z) SELECT x,y,4 FROM db1.a 
INSERT INTO db2.b (x, y, z) SELECT x, y, 4 FROM db1.a; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
