1

This previous questions almost has my answer: How to update from select with a Join

That questions solution was:

UPDATE invoiceLine
INNER JOIN terminal
    ON invoiceLine.ctn = terminal.ctn
INNER JOIN network
    ON terminal.network = network.id
SET invoiceLine.network = network.label

What I am truly trying to do is combine(permanently store Table2's data in Table1) two tables that share a common primary key. Table1 has 49 fields while Table2 has 104 fields.

What if I want to update over 100 fields?

Do I need to SET invoiceLine.network = network.label, invoiceLine.item1 = network.item1, etc... all the way to invoiceLine.item103 = network.item103?

Is there an easier way to JOIN two tables AND set it in the database?

SELECT *
FROM test2
INNER JOIN test1 ON test1.`API_ID` = test2.`API_ID`

This does what I want, except it doesn't SET the data in test1.

Community
  • 1
  • 1
Lord Pickle
  • 33
  • 3

1 Answers1

1

Yes, you have to, you can also do an INSERT TO ... SELECT (select can be a join of many tables) but then you still have to define your fields, so there is little gain timewise. important: you need equal amount of fields in the insert as in the select.

INSERT INTO TABLE1 (list, of, fields) SELECT list, of, fields FROM TABLE2;
Jeroen
  • 982
  • 1
  • 6
  • 14