MySQL: insert values from another table into one column

Question

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!

Bill Karwin · Accepted Answer · 2013-01-09 00:48:48Z

15

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`

answered Jan 9, 2013 at 0:48

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tadman Over a year ago

This is possible because MySQL doesn't care about the type specified and will convert accordingly.

DWright · Accepted Answer · 2013-01-09 00:51:34Z

0

You definitely can do this. See the helpful answers on this. However, you should consider whether this is a good idea. Once you denormalize distinct columns into some kind of string expression in one column, you've created something that is very difficult to maintain and also to query against. You are making your data less usable to yourself in doing this.

answered Jan 9, 2013 at 0:51

DWright

9,5594 gold badges39 silver badges57 bronze badges

1 Comment

DWright Over a year ago

@Joseph: Hard to say on this one, for me. I considered making it a comment. But then I felt that the guidance and mentoring aspect of SO was also important to the question in this case.

Chamila Chulatunga · Accepted Answer · 2013-01-09 00:54:40Z

0

You can use a concatenation for a simple case, e.g:

INSERT INTO items_rooms (
  item_id,
  room_id,
  x_and_y,
  n)
SELECT
  id,
  room_id,
  CONCAT(x, '.', y),
  z
FROM `items_phoenix`

edited Jan 9, 2013 at 0:54

answered Jan 9, 2013 at 0:49

Chamila Chulatunga

4,95418 silver badges17 bronze badges

2 Comments

Bill Karwin Over a year ago

Microsoft SQL Server uses + for string concatenation, but MySQL doesn't.

Chamila Chulatunga Over a year ago

Not a MySQL expert, so originally had SQL Server syntax (@Zaffy, I did have + "." + in the original edit, so it wasn't arithmetic addition). Edited to reflect correct MySQL syntax.

Sir Rufo · Accepted Answer · 2013-01-09 00:59:53Z

0

Yes, if the column can store text information

Just CONCAT or CONCAT_WS the values

INSERT INTO items_rooms ( item_id, room_id, my_value, n )
SELECT id, room_id, CONCAT_WS( '.', x, y ), z
FROM `items_phoenix`

SQL Fiddle DEMO

edited Jan 9, 2013 at 0:59

answered Jan 9, 2013 at 0:50

Sir Rufo

19.3k3 gold badges47 silver badges79 bronze badges

Comments

Jonny Barry · Accepted Answer · 2013-01-09 00:53:14Z

-2

$table_To_Get_From = mysql_query("SELECT * FROM items_phoenix (id, room_id,x,y,z)");
if($table_To_Get_From){
    $table = mysql_fetch_assoc($table_To_Get_From);
    $values = array($table['id'],$table['room_id'],$table['x'],$table['y'],$table['z']);
    mysql_query("INSERT INTO items_rooms (item_id, room_id,x,y,n)  
        VALUES ($values[0],$values[1],$values[2],$values[3],$value[4])");
}

I didn't make them strings, so you may need to use '$values[num]'

This will insert it like you wanted, if there is any values.

answered Jan 9, 2013 at 0:53

Jonny Barry

51 bronze badge

1 Comment

Sir Rufo Over a year ago

how do you know the OP can use PHP? What if he uses Delphi, C++, or whatever. The question is only about MySQL

Collectives™ on Stack Overflow

MySQL: insert values from another table into one column

5 Answers 5

1 Comment

1 Comment

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related