Copy columns from table1 to table2

[Pedroski55]

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??

[Pedroski55]

I found a way to do what I want, but it is big and bulky.

Can anyone make it smaller?

    # FOR CLASS 20BE1
    # this works!! BUT IS VERY LARGE IF I COPY 5 COLUMNS
    UPDATE allstudents20BE SET 
allstudents20BE.email = (SELECT allstudents20BE1.email FROM allstudents20BE1 WHERE allstudents20BE1.studentnr = allstudents20BE.studentnr AND allstudents20BE1.has_been_inc = 1), allstudents20BE.password = (SELECT allstudents20BE1.password FROM allstudents20BE1 WHERE allstudents20BE1.studentnr = allstudents20BE.studentnr AND allstudents20BE1.has_been_inc = 1),
    allstudents20BE.max_attendance = (SELECT allstudents20BE1.max_attendance FROM allstudents20BE1 WHERE allstudents20BE1.studentnr = allstudents20BE.studentnr AND allstudents20BE1.has_been_inc = 1),
    allstudents20BE.attendance = (SELECT allstudents20BE1.attendance FROM allstudents20BE1 WHERE allstudents20BE1.studentnr = allstudents20BE.studentnr AND allstudents20BE1.has_been_inc = 1),
    allstudents20BE.has_been_inc = (SELECT allstudents20BE1.has_been_inc FROM allstudents20BE1 WHERE allstudents20BE1.studentnr = allstudents20BE.studentnr AND allstudents20BE1.has_been_inc = 1),
    allstudents20BE.logon_time = (SELECT allstudents20BE1.logon_time FROM allstudents20BE1 WHERE allstudents20BE1.studentnr = allstudents20BE.studentnr AND allstudents20BE1.has_been_inc = 1)