MySQL-在另一个表中将值从一列复制到另一列

I have a table a1 like this

我有一张像这样的表a1

number    |   name
1             John
2             Jake
3             Linda

And another table a2 with exact same format as a1 (2 columns number and name) but has no values at all. How can I copy values from a1.numberto a2.number ?

另一个表a2的格式与a1完全相同(2列数和名称),但根本没有值。如何从a1.numberto a2.number复制值?

I tried

UPDATE a2, a1 SET a2.number = a1.number

but it does not work

但它不起作用

3 个解决方案

#1

If your second table has no data, then you want to use insert:

如果您的第二个表没有数据,那么您想使用insert:

insert into t2(number, name)
    select number, name
    from t1;

#2

If your second table already have rows and you want to update the num column Try this:

如果您的第二个表已经有行并且您想要更新num列试试这个:

update a2
inner join a1 on a1.id = a2.id
set a2.num = a1.num

Else you should go with Gordon Linoff's answer

另外你应该选择Gordon Linoff的答案

#3

have you tried to use

你试过用吗?

insert into a2 
    select a1.number 
    from a1;

if you really just want to copy column number of a1 to a2, then

如果你真的只想将a1的列号复制到a2,那么

update a1 
join a2 on a1.number = a2.number 
set a2.name = '';

#1