Insert from two tables where condition

Question

I have 3 tables

Table 1 EIN columns :

id, name, plate,in_datetime,time,image-name.

Table 2 EOUT columns :

id, name, plate, out_datetime, time, image-name.

Table 3 recon columns :

id, plate,in_datetime,in_entry_id,out_datetime, out_entry_id,Processed, duration.

I need to get table 1 and table 2 values into table 3 columns using where condition. I am using the below query:

INSERT INTO recon (id,EIN.plate,EIN.in_datetime, EIN.id,  EOUT.out_datetime, EOUT.id,null, null) 
    SELECT EIN.RegistrationMark,EIN.datetime,EIN.id, EOUT.date,EOUT.id FROM EIN_anpr_vega as EIN, EOUT_anpr_vega as  EOUT
    where EIN.plate = EOUT.plate
    and EIN.in_datetime = EOUT.out_datetime

I am getting an error near null, null. below is the error.

for the right syntax to use near 'null, null

Possible duplicate of [MySQL Insert into multiple tables? (Database normalization?)](https://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization) — Nico Haase, Jan 04 '18 at 11:12

score 1 · Answer 1 · answered Jan 04 '18 at 11:08

In your column names you cant write null

INSERT INTO recon (id,EIN.plate,EIN.in_datetime, EIN.id,  EOUT.out_datetime, EOUT.id,null, null)

Instead, write the column names and place the nulls in the select. Like this:

INSERT INTO recon (id,EIN.plate,EIN.in_datetime, EIN.id,  EOUT.out_datetime, EOUT.id,col1, col2) 
    SELECT EIN.RegistrationMark,EIN.datetime,EIN.id, EOUT.date,EOUT.id, null, null FROM EarlsdonMSIN_anpr_vega as EIN, EarlsdonMSOUT_anpr_vega as  EOUT
    where EIN.plate = EOUT.plate
    and EIN.in_datetime = EOUT.out_datetime

score 0 · Answer 2 · answered Jan 04 '18 at 22:37

INSERT INTO recon (id, EIN.plate ...) VALUES ...

The things inside the first () are supposed to be column names for the table recon. (I'm surprised that it did not show a syntax error with EIN.plate.)

Yes, later, null will get into trouble for the same reason -- it is not a column name.

Insert from two tables where condition

2 Answers2