CODEWITHSUNDEEP
mysqlupdates
Stan
Stan

Reputation: 107

UPDATE column based on another table

I have two tables, TableA and TableB. Common column is the ID.

Table_A                        Table_B
ID | Tshirt |    Jeans  |      ID |  Jeans    |  
-------------------------      ----------------
1  | small  |  (empty)  |      1  | medium    |
2  | medium |  (empty)  |      2  | small     |
3  | large  |  (empty)  |      3  | medium    |

How can I update the column "Jeans" in Table A with data from column "Jeans" from Table B? The column "Jeans" already exists with empty values in Table A.

Result should be:

 Table_A                        
    ID | Tshirt |    Jeans  |    
    -------------------------   
    1  | small  |  medium  |   
    2  | medium |  small   |      
    3  | large  |  medium  |

I have used:

INSERT INTO Table_A SELECT Table_A.Jeans 
FROM Table_A JOIN TableB ON Table_A.id = Table_B.id;

Error I am getting is: "Error Code: 1136. Column count doesn't match value count at row 1".

What am I doing wrong?

Upvotes: 0

Views: 52

Answers (2)

guido
guido

Reputation: 19194

You are trying to insert new rows, not updating them, and the error you get is because the number of columns selected by the inner query (1) is different than the number of colukmn in the target table (3). Use an update instead:

update Table_A set Jeans = (select Jeans from Table_B where Table_B.id = Table_a.id)

Upvotes: 1

Dan
Dan

Reputation: 11084

You should be using an UPDATE query

UPDATE Table_A a 
JOIN Table_B b ON a.id = b.id
SET a.jeans = b.jeans;

Upvotes: 1

Related Questions