Moving data from one table row to another in MySQL

Question

I have two tables, first one A, is full of information. Second one B, is empty. I want to take a row from table A and put it into table B. So far I have to start a row(filling in two of the columns) in table B, then update. Is there way to just create a new row with the other tables info for the row I want, same schema. I was looking at LEFT JOIN, but couldn't break down how to add it by row. Below is what I have so far:

UPDATE tableB
SET tableB.aaa = (SELECT aaa FROM tableA where id = '1'),
    tableB.bbb = (SELECT bbb FROM tableA where id = '1'),
    tableB.ccc = (SELECT ccc FROM tableA where id = '1'),
    tableB.ddd = (SELECT ddd FROM tableA where id = '1'),
    tableB.eee = (SELECT eee FROM tableA where id = '1'),
where proprietaryname='drugC';

Also, is there a way to not make this repetitive. New to SQL, appreciate the help.

`tableB` is empty right? Then how can you select data from it? — Ullas, Jun 30 '16 at 17:37
http://stackoverflow.com/questions/15209414/how-to-do-3-table-join-in-update-query — Matt, Jun 30 '16 at 17:38
My mistake update question, had table switched in code, same logic applies — Ryan, Jun 30 '16 at 17:42

score 1 · Accepted Answer · answered Jun 30 '16 at 17:46

1

If you want to insert new rows in tableB from tableA, then you can use insert . . select:

insert into tableB(aaa, bbb, ccc, ddd, eee)
    select aaa, bbb, ccc, ddd, eee
    from tableA
    where proprietaryname = 'drugC';

answered Jun 30 '16 at 17:46

Gordon Linoff

1,242,037
58
646
786

Moving data from one table row to another in MySQL

1 Answers1