多表关联插入Recon表遇语法错误：near 'null, null'求助

Hey there, let's break down and fix that syntax error you're hitting. The near 'null, null' error happens because you can't put literal null values in the column list of an INSERT statement—this list should only contain the names of columns from your target table (recon), not the values you want to insert. Values belong in the SELECT clause instead.

First, let's align your field mappings correctly based on the tables you described. Here's the corrected SQL that addresses the syntax issue and fixes some field mismatches in your original query:

INSERT INTO recon (plate, in_datetime, in_entry_id, out_datetime, out_entry_id, Processed, duration)
SELECT 
    EIN.plate,
    EIN.in_datetime,
    EIN.id,
    EOUT.out_datetime,
    EOUT.id,
    NULL,  -- Assigns NULL to the Processed column
    NULL   -- Assigns NULL to the duration column
FROM EIN_anpr_vega AS EIN
JOIN EOUT_anpr_vega AS EOUT 
    ON EIN.plate = EOUT.plate 
    AND EIN.in_datetime = EOUT.out_datetime;

Key Fixes & Explanations: #

• Removed null from the column list: The INSERT column list now only includes valid column names from the recon table. We moved the NULL values to the SELECT clause, where they map directly to the Processed and duration columns.
• Fixed field name mismatches: Your original query had typos like EIN.RegistrationMark (your EIN table uses plate) and EIN.datetime (should be EIN.in_datetime). We corrected these to match your table schemas.
• Switched to explicit JOIN: Using JOIN ... ON instead of comma-separated tables makes the query more readable and follows modern SQL best practices.
• Handled the recon.id column: If recon.id is an auto-incrementing primary key (common for ID columns), you don't need to include it in the INSERT list—your database will generate a value automatically. If you need to set it manually, add it to the column list and include a corresponding value in the SELECT clause.

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