MySQL update column in new table with content from another table (move)

Question

I have these tables (edited out irelevant fields)

mysql> desc studentcourseplan;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| cpl_id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| student_id        | int(11)      | YES  | MUL | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+


mysql> desc studentdates;
+-----------------------+---------+------+-----+---------+----------------+
| Field                 | Type    | Null | Key | Default | Extra          |
+-----------------------+---------+------+-----+---------+----------------+
| student_date_id       | int(11) | NO   | PRI | NULL    | auto_increment |
| student_id            | int(11) | YES  |     | NULL    |                |
| student_date_cpl_id   | int(11) | YES  |     | NULL    |                |
+-----------------------+---------+------+-----+---------+----------------+

I would like to copy the column studentcourseplan.cpl_id to studentdates.student_date_cpl_id using the below query (to exclude duplicates)

SELECT cpl_id FROM studentcourseplan
    WHERE student_id NOT IN ('50', '51', '85', '86', '90', '95', '89', '91', '92', '93', '94', '97', '98', '99', '100', '88')

When I do a normal INSERT INTO studentdates.student_date_cpl_id SELECT ...... it adds to new rows, and does not update the current rows. How would I achieve this?

Take a look: http://stackoverflow.com/questions/725556/how-can-i-merge-two-mysql-tables — dani herrera, Sep 24 '12 at 08:53

AnandPhadke · Accepted Answer · 2012-09-24T09:05:18.170

1

Update  studentdates s,studentcourseplan sp
SET s.student_date_cpl_id = sp.cpl_id
where s.student_id=sp.student_id            
and s.student_id not in('50', '51', '85', '86', '90', '95', '89', '91', '92', '93', '94', '97', '98', '99', '100', '88')

edited Sep 24 '12 at 09:05

answered Sep 24 '12 at 08:58

AnandPhadke

13,160
5
26
33

score 1 · Answer 2 · answered Sep 24 '12 at 09:00

1

try something like this

INSERT INTO studentdates.student_date_id,
     studentdates.student_date_cpl_id
select .... (join your tables)

ON DUPLICATE KEY UPDATE studentdates.student_date_cpl_id = studentcourseplan.cpl_id

answered Sep 24 '12 at 09:00

RomanKonz

1,027
1
8
15

MySQL update column in new table with content from another table (move)

2 Answers2