Ryan Ryan - 5 months ago 11
SQL Question

Moving data from one table row to another in MySQL

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.

Answer

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';