CODEWITHSUNDEEP
mysql
Hene
Hene

Reputation: 159

Insert mysql column record to second table with different column name

I have a table called

order

| id | created | customer_id | ... | ... |

I would like to move the columns created and customer_id etc. with records to a second table called

order_meta

| id | order_id | meta | value |

so that the meta is "created" and value is created value from order's table

Is this possible to do with the mysql query or do I need to create an script for this?

Upvotes: 1

Views: 54

Answers (2)

Rahi
Rahi

Reputation: 1485

INSERT INTO order_meta (order_id, meta, value) 

SELECT id, 'created', created

FROM order

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Is this what you want?

insert into order_meta (order_id, meta, value)
    insert id, 'created', created
    from orders;

If you want to denormalize more columns, you can do:

insert into order_meta (order_id, meta, value)
    insert id, m.meta,
           (case meta when 'created' then created
                      when 'customer_id' then customer_id
            end)
    from orders o cross join
         (select 'created' as meta union all
          select 'customer_id' as meta
         ) m

Upvotes: 1

Related Questions