Basically, I want to copy 3 columns from table1 to table2, which both have the same column names.
My table1: allstudents20BE1 has 9 columns: id, studentnr, name, email, password, max_attendance, attendance, has_been_inc, logon_time
table2： allstudents20BE has exactly the same columns.
The only difference is the column id. id is the PRIMARY KEY
The table allstudents20BE1 has id from 1 to 41
The table allstudents20BE has id from 1 to 168
I’ve been trying all morning to get this working. I made the studentnr column a UNIQUE field.
REPLACE INTO allstudents20BE (max_attendance, attendance, has_been_inc) SELECT allstudents20BE1.max_attendance, allstudents20BE1.attendance, allstudents20BE1.has_been_inc FROM allstudents20BE1 INNER JOIN allstudents20BE WHERE allstudents20BE1.has_been_inc != 0 AND allstudents20BE1.studentnr = allstudents20BE.studentnr
The problem with the above is: it transfers the values in the columns max_attendance, attendance, has_been_inc fine, but it inserts a new row, with the studentnr column as the default: 1
This command ignores the part in the condition:
WHERE allstudents20BE1.has_been_inc != 0 AND allstudents20BE1.studentnr = allstudents20BE.studentnr
How can I make it obey the condition??