Menu
I am wanting to move items from one table into another using MySQL.
This is what I currently use:
INSERT INTO items_rooms (item_id, room_id,x,y,n)
SELECT id, room_id,x,y,z
FROM `items_phoenix`
This works, however I am wanting to insert multiple values into one column instead of each values in different columns.
For example:
In table items_rooms, I would like values x and y from items_phoenix to be placed into one column in items_rooms.
If x = 5 and y = 2, can I save it into items_rooms like this: one column: 5.2 instead of different columns for each values?
Sorry if this is confusing!
554
First, you must specify the name of the table. After that, in parenthesis, you must specify the column name of the table, and columns must be separated by a comma. The values that you want to insert must be inside the parenthesis, and it must be followed by the VALUES clause.
The SQL INSERT INTO SELECT Statement The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.
First, specify the name of table that you want to insert after the INSERT INTO keywords. Second, specify a comma-separated column list inside parentheses after the table name. Third, specify a comma-separated list of row data in the VALUES clause. Each element of the list represents a row.
You can use expressions in your SELECT column-list. As long as the columns in the select-list and the columns in the destination table match in number and data type, you can do something like this:
INSERT INTO items_rooms (item_id, room_id, x_dot_y, n)
SELECT id, room_id, CONCAT(x,'.',y), z
FROM `items_phoenix`
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
