CODEWITHSUNDEEP
mysql
qadenza
qadenza

Reputation: 9293

copy columns between two tables without inserting new rows

table1 - columns: id, date,

table2 - columns: id, date, img, auth, moder, tags, visits

table1 - 1200 rows
table2 - 1000 rows

I want to copy missing columns from table2 to table1 (img, auth, moder, tags, visits).

regardless of values (this are all test data, doesn't matter what value is on a certain row).

I tried firstly to create missing columns in table1 and then: 

insert into table1 (img, auth, moder, tags, visits) select img, auth, moder, tags, visits from table2;

But this inserts new rows into table1. I don't want new rows - just add new columns having data from table2.

Upvotes: 0

Views: 21

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

It looks like what you want here is an update, not an insert, since you don't actually want to add new records, just modify ones which already exist.

UPDATE table1 t1
INNER JOIN table2 t2
    ON t1.id = t2.id AND t1.date = t2.date
SET
    t1.img = t2.img,
    t1.auth = t2.auth,
    t1.moder = t2.moder,
    t1.tags = t2.tags,
    t1.visits = t2.visits;

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

use update 

update table1 
join table2 on table1.id=table2.id and table1.date=table2.date
set  tabl1.img=tabl2.img, table1.auth=table2.auth, table1.moder=table1.moder,
table1.tags=table2,tags, table1.visits=table2.visits

Upvotes: 1

Related Questions