MySQL. Two tables, copy one column

Question

I am learning MySQL and I have an issue. I have two tables.. table1 and table2. table1 contains several columns such as (id, type, id_marca, price) etc and table2 has several columns such as (id, values, .., id_marca). What I want and what I'm trying to do is: id_marca in the first table has values and the id_marca in the second table has NULL values. I want to copy the values from id_marca.table1 into id_marca.table2. Basically copy the column in the first table into the second one.

I used

INSERT INTO table2 (id_marca) SELECT  id_marca  FROM table1 ;

But the issue is the following.. it inserts the values of the column in the first table AFTER the NULL values and does not replace them.

To see the issue better: This is table1:

id    name    id_marca
1      a         1
2      b         1 
3      c         2

This is table2:

id   value   id_marca
1      123         NULL
2      34155       NULL
3      123         NULL

After I execute INSERT INTO table2 (id_marca) SELECT id_marca FROM table1 , table 2 becomes:

id   value   id_marca
1      123         NULL
2      34155       NULL
3      123         NULL
4       0            1
5       0            1
6       0            2

But I want it to be:

id   value   id_marca
1      123         1
2      34155       1
3      123         2

Hope you will understand, thank you in advance guys.

yes you are doing insert so it adding new row you are probably looking for update and I suppose its continuation of http://stackoverflow.com/questions/23671959/mysql-two-tables-displaying-information-from-both-tables/23672178#23672178 — Abhik Chakraborty, May 15 '14 at 09:06
@AbhikChakraborty No, it's not the continuation of that. Different things. :) — MrSilent, May 15 '14 at 09:07
I meant by continuation that you are now adding the id_marca in the 2nd table for joining the tables :) — Abhik Chakraborty, May 15 '14 at 09:08

score 3 · Accepted Answer · answered May 15 '14 at 09:08

3

You should use UPDATE not INSERT and if these tables is logically linked by ID field then try:

UPDATE TABLE2 a 
    JOIN TABLE1 b ON a.id = b.id
SET a.id_marca = b.id_marca

answered May 15 '14 at 09:08

valex

23,966
7
43
60

1

well the problem is this OP already posted this before and he does not have a relation between the tables and we suggested him to make a relation to do join. So this will not work. – Abhik Chakraborty May 15 '14 at 09:09

score 0 · Answer 2 · answered Jun 12 '19 at 16:23

Context

MySQL v 5.7
User wants to copy one table column to another table using INSERT
The goal is to change BEFORE into AFTER

Solution

This can be accomplished using MySQL INSERT ON DUPLICATE KEY UPDATE
This approach will insert new records, and it will also update existing records having matching ids

Example

INSERT INTO zzdemo_table02
  (lname,userid)
SELECT
  lname,userid
  FROM(
    SELECT
      lname,userid
    FROM
      zzdemo_table01
  ) as tt01
ON DUPLICATE KEY UPDATE
  userid=tt01.userid
  ,lname=tt01.lname
;

MySQL. Two tables, copy one column

2 Answers2

Context

Solution

Example

See also

Linked