Where clause in Insert command in mysql

Question

I need to do something like this

insert into tableA(code) select code from tableB where id=tableB.id;

I cant insert the code until both id are matched. How do i do this?

its there in both. so i need to match the id's of both when it inserts the data — Aarushi
– Aarushi, Commented Jan 28, 2016 at 7:41
if the id is already in tableA so why you want to insret data on it ? by the way using join you can acomplish this — Vipin Jain
– Vipin Jain, Commented Jan 28, 2016 at 7:43
tableA(id,code, some other 7-8 columns) tableB(id,code,some other 2-3 columns) Now i need to insert tableB.code into tableA.code matching the id of both — Aarushi
– Aarushi, Commented Jan 28, 2016 at 7:45

Rahul · Accepted Answer · 2016-01-28 07:52:41Z

1

You can either do a join or use where exists like

insert into tableA(code) 
select tb.code 
from tableB tb 
join tableA on tableA.id = tableB.id;

(OR)

insert into tableA(code) 
select tb.code 
from tableB tb where exists(select 1 from tableA 
where id = tb.id);

Looking at your comment, looks like you rather need a UPDATE statement like

UPDATE tableA a 
    JOIN tableB b ON a.id = b.id 
SET a.code = b.code;

answered Jan 28, 2016 at 7:44

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

2 Comments

it says ERROR 1052 (23000): Column 'code' in field list is ambiguous

Yes, since code column present in both table. BTW, looks you need a update command rather not insert. Look into the edit in answer if that helps.

Vipin Jain · Accepted Answer · 2016-01-28 08:51:24Z

0

INSERT INTO tableA.field1,tableA.field2,......
SELECT tableB.field1,tableB.field2,......
FROM tableB  
JOIN tableA ON tableA.id = tableB.id;

answered Jan 28, 2016 at 8:51

Vipin Jain

3,77618 silver badges36 bronze badges