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 stackoverflow.com/questions/23671959/… — Abhik Chakraborty
– Abhik Chakraborty, Commented May 15, 2014 at 9:06
@AbhikChakraborty No, it's not the continuation of that. Different things. :) — MrSilent
– MrSilent, Commented May 15, 2014 at 9:07
I meant by continuation that you are now adding the id_marca in the 2nd table for joining the tables :) — Abhik Chakraborty
– Abhik Chakraborty, Commented May 15, 2014 at 9:08

valex · Accepted Answer · 2014-05-15 09:08:25Z

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, 2014 at 9:08

valex

24.2k7 gold badges47 silver badges62 bronze badges

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

1 Comment

Abhik Chakraborty Over a year ago

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.

dreftymac · Accepted Answer · 2019-06-12 16:23:16Z

0

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
;

Collectives™ on Stack Overflow

MySQL. Two tables, copy one column

2 Answers 2

1 Comment

Context

Solution

Example

See also

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Context

Solution

Example

See also

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related