JOIN statement with MySQL

Question

I'm struggling with joining two tables with MySQL.

The first table is a subset of customers and is called "merge1", and contains the following records:

    customer_id   First Name      Total Spent    Total Orders
    ==========================================================
    1              Mike              0.00          0.00
    2              Allan             0.00          0.00
    3              Craig             0.00          0.00
    4              Michael           0.00          0.00

Table 2 has sales data and is as follows and is called "merge2", and contains the following records:

    customer_id   Total Spent   Total Orders
    =========================================
    1                65.00            1
    2                64.00            1
    3               109.00            1
    4                65.00            1

I'm trying to update the "Total Spent" and "Total Orders" in the first table (merge 1) by runninng the following SQL:

    INSERT INTO `merge1` (`customer_id`, `Total Spent`, `Total Orders`)

    SELECT 
        merge2.`customer_id`,
        merge2.`Total Spent`,
        merge2.`Total Orders`

    FROM  
      merge2

    JOIN
        merge1 ON merge2.customer_id = merge1.customer_id;

The query doesn't error out but the table isn't updating.

Can someone point me in the right direction, I'd really appreciate your help!?

Answer

Thanks heaps for the comments below! I should have used the "Update" command. Here is the syntax I used if it comes in handy for others.

    UPDATE merge1 T1 JOIN merge2 T2
    SET    T1.`Total Spent` = T2.`Total Spent`, T1.`Total Orders` = T2.`Total Orders`
    WHERE  T1.customer_id = T2.customer_id;

Possible duplicate of [How to use JOIN in UPDATE query?](http://stackoverflow.com/questions/15209414/how-to-use-join-in-update-query) — Jens, Dec 11 '15 at 10:49
If your keys are primary, It will throw an error for your query — Aravindhan, Dec 11 '15 at 10:49
If you want to **update** some records then write an [`UPDATE`](http://dev.mysql.com/doc/refman/5.7/en/update.html) query, not an `INSERT` one. — axiac, Dec 11 '15 at 10:49
Please don't edit question posts to contain answers. If the final answer isn't exactly in your accepted answer post, post it in an answer post. PS This is clearly a faq. Please in code questions give a [mre].. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Jun 22 '20 at 12:19

Ajay Makwana · Accepted Answer · 2020-06-22T05:17:00.773

hey there if you need to insert then use :

INSERT INTO `merge1`(`customer_id`, `Total Spent`, `Total Orders`) SELECT 
        merge2.`customer_id` AS customer_id,merge2.`Total Spent` AS Total Spent,merge2.`Total Orders` AS Total Orders FROM merge2

OR if you need to updtae then use :

UPDATE `merge1` JOIN `merge2` ON merge1.customer_id = merge2.customer_id
SET merge1.`Total Spent` = merge2.`Total Spent`,merge1.`Total Orders` = merge2.`Total Orders`

Let me know what happens :)

JOIN statement with MySQL

Answer

1 Answers1