reset password
Author Message
harry_520
Posts: 76
Posted 15:13 Mar 16, 2014 |

I'm trying to copy the records in the courses table to a new table, which I named it quarters, every time when enter the course planner portion of the program. But every time when the action happens, duplicates happends because it doesn't check if the record has already existed in the quarters table. How do I avoid this?

Last edited by harry_520 at 22:59 Mar 16, 2014.
cysun
Posts: 2935
Posted 22:10 Mar 16, 2014 |

First of all, you should just copy course id or code code, not the whole record. Secondly, having "duplicates" in your quarters table is fine because a course may appear in multiple course plans.

harry_520
Posts: 76
Posted 22:23 Mar 16, 2014 |

What I meant was the copy action shouldn't copy all the records inside the courses table again and again every time when displaying the "Please select the courses you have already taken" page. It should check and only copy the newly added records which has not been inserted into the quarters table yet. How do I perform this action?

cysun
Posts: 2935
Posted 22:28 Mar 16, 2014 |
harry_520 wrote:

What I meant was the copy action shouldn't copy all the records inside the courses table again and again every time when displaying the "Please select the courses you have already taken" page. It should check and only copy the newly added records which has not been inserted into the quarters table yet. How do I perform this action?

It sounds like you shouldn't copy the courses in the first place.

harry_520
Posts: 76
Posted 22:53 Mar 16, 2014 |

Let's just say, I want to initialize the course planner with the copy action of copying the courses to a new table to store, but every time the copy action happens, it finds out the records already exist, and displays the error message "com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'CS120' for key 'PRIMARY'
". Dispite the primary key issue, how do I only copy the courses which doesn't exist in the new table.

Last edited by harry_520 at 23:03 Mar 16, 2014.
cysun
Posts: 2935
Posted 23:51 Mar 16, 2014 |
harry_520 wrote:

Let's just say, I want to initialize the course planner with the copy action of copying the courses to a new table to store, but every time the copy action happens, it finds out the records already exist, and displays the error message "com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'CS120' for key 'PRIMARY'
". Dispite the primary key issue, how do I only copy the courses which doesn't exist in the new table.

You can do something like:

insert into table2 (select * from table1 where course_code not in (select course_code from table2))

But like I said, you probably shouldn't do this in the first place.