求助：MySQL多表INSERT INTO语句异常，需合并用户课程数据

Hey there! Let's break down what's going wrong with your current approach and get you the correct result.

The Problem with Your Current Code #

When you run your second INSERT INTO temptest (Kurs1)... statement, you're adding new rows to the table instead of updating the existing user rows you inserted first. That's why you're seeing split entries—one row with Name/ID and NULL Kurs1, another row with NULL Name/ID and the Kurs1 value.

Solution 1: Populate the Table in One Go (Recommended) #

Instead of splitting the operation into two steps, use a LEFT JOIN to pull user data and their course 19 completion time in a single insert. This ensures each user gets a single row with all relevant data (and Kurs1 will be NULL if they haven't completed the course).

First, make sure your temptest table is properly created (adjust data types to match your actual tables):

CREATE TABLE temptest (
    Name VARCHAR(255), -- Match the length of user.email
    ID INT, -- Match user.id's data type
    Kurs1 DATETIME -- Match course_completions.timecompleted's data type
);

Then run this insert statement:

INSERT INTO temptest (Name, ID, Kurs1)
SELECT
    u.email AS Name,
    u.id AS ID,
    cc.timecompleted AS Kurs1
FROM user u
LEFT JOIN course_completions cc
    ON u.id = cc.userid
    AND cc.course = 19; -- Filter for course 19 only

Solution 2: Update Existing Rows (If You Already Inserted User Data) #

If you already have the user rows in temptest and just need to fill in the Kurs1 values, use UPDATE instead of INSERT. Also, it's better to join on ID (the primary key) instead of email to avoid issues with duplicate emails:

UPDATE temptest t
JOIN user u ON t.ID = u.id
JOIN course_completions cc ON u.id = cc.userid AND cc.course = 19
SET t.Kurs1 = cc.timecompleted;

Quick Tip #

Always prefer joining on primary keys (like user.id) over non-unique fields (like email)—this ensures you're targeting the exact user you intend to update, avoiding accidental mismatches.

内容的提问来源于stack exchange，提问作者Nimbua Peter