Insert data in a table from two other tables

Question

I need to insert data in a table from two other tables that have the same schema.

I have an exception :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL JOIN data e2 ON e1.siren = e2.siren' at line 3

Here's My code :

string MergeTables = string.Format(@"INSERT INTO Table3 (a,b) 
SELECT e1.a, e2.b
FROM bilan e1 FULL JOIN data e2
ON e1.id= e2.id;

");

MySQL does not support `FULL JOIN` – Madhur Bhaiya Sep 18 '18 at 10:02 — Madhur Bhaiya, Sep 18 '18 at 10:02
Side note: `Format` is *redundant* here – Dmitry Bychenko Sep 18 '18 at 10:03 — Dmitry Bychenko, Sep 18 '18 at 10:03
https://stackoverflow.com/questions/7978663/mysql-full-join – Dmitry Bychenko Sep 18 '18 at 10:04 — Dmitry Bychenko, Sep 18 '18 at 10:04
Thank you @DmitryBychenko !! – Coder-Meca Sep 18 '18 at 10:27 — Coder-Meca, Sep 18 '18 at 10:27
Thank you @MadhurBhaiya !! – Coder-Meca Sep 18 '18 at 10:27 — Coder-Meca, Sep 18 '18 at 10:27

score 0 · Accepted Answer · answered Sep 18 '18 at 10:03

0

Try to remove ; at the end of your sql and remove FULL due to MySQL do not support FULL JOIN

  string MergeTables = string.Format(@"INSERT INTO exercices (AF,region) 
  SELECT e1.AF, e2.region FROM bilan e1 
    JOIN data e2 ON e1.siren = e2.siren
   ");

answered Sep 18 '18 at 10:03

flyingfox

13,414
3
24
39

I did that values inserted to third table correctly but i have another exception in the end of the method " Fatal error encountered during command execution. " here my code ( var conn = new MySqlConnection(dbConnectionString); conn.Open(); var cmd = new MySqlCommand(MergeTables, conn); cmd.ExecuteNonQuery(); ) – Coder-Meca Sep 18 '18 at 10:19
@mecab95 You can update your question(then cancel my answer) or open a new question with the deital information of the error – flyingfox Sep 18 '18 at 11:17

score 0 · Answer 2 · answered Sep 18 '18 at 10:05

0

MySQL does not support FULL JOIN

Try to emulate it as follows:

SELECT e1.AF FROM bilan e1
LEFT JOIN data e2 ON e1.siren = e2.siren
UNION
SELECT e2.region FROM data e2
RIGHT JOIN bilan e1 ON e2.siren = e1.siren

answered Sep 18 '18 at 10:05

Madhur Bhaiya

28,155
10
49
57

Insert data in a table from two other tables

2 Answers2