# Copy columns from table1 to table2

**URL:** <https://www.phphelp.com/t/copy-columns-from-table1-to-table2/33840>\
**Category:** MySQL database\
**Created:** [August 23, 2021, 6:21am UTC](https://www.phphelp.com/t/copy-columns-from-table1-to-table2/33840 "2021-08-23T06:21:37Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pedroski55](https://www.phphelp.com/letter_avatar_proxy/v4/letter/p/a4c791/32.png) [@Pedroski55](https://www.phphelp.com/u/Pedroski55)\
**Post date:** [August 23, 2021, 6:21am UTC](https://www.phphelp.com/t/copy-columns-from-table1-to-table2/33840/1 "2021-08-23T06:21:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Pedroski55](https://www.phphelp.com/letter_avatar_proxy/v4/letter/p/a4c791/32.png) [@Pedroski55](https://www.phphelp.com/u/Pedroski55)\
**Post date:** [August 23, 2021, 11:57pm UTC](https://www.phphelp.com/t/copy-columns-from-table1-to-table2/33840/2 "2021-08-23T23:57:54Z")

</div>

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)
```