1

I have two tables. Two tables have same fields and two table have some data. Now I want to select data in table1 and insert the the data in to table2. But I am using between, so i am confused. Please help me... Insert data in to table2 with out duplicate value.

INSERT INTO table2 
  (`student_id`, `studentname`, `Regno`, `class`, `date`, `session`
   , `status`, `teacher_id`) 
  SELECT * FROM table1, table2 
  WHERE table1.date <> table2.date
    BETWEEN '2011-01-01'
    AND '2011-05-19' AND table1.class = 'AAA'
2
  • Does table2 have a primary key? How do you define duplicate? All columns are the same or just the first ... etc? Commented Aug 25, 2011 at 8:10
  • I would expect student_id to be the PK (or maybe the combo (student_id + teacher_id) Commented Aug 25, 2011 at 8:23

1 Answer 1

2

You're doing a cross join on inequality which will generate an huge amount of (duplicate) rows.
Instead you should do a LEFT JOIN on equality and filter out the null rows.

I'd rewrite it to:

INSERT INTO table2 
  (`student_id`, `studentname`, `Regno`, `class`, `date`, `session`
   , `status`, `teacher_id`) 
SELECT t1.* FROM table1 t1
LEFT JOIN table2 t2 ON (t1.student_id = t2.student_id)
WHERE t1.`date` BETWEEN '2011-01-01' AND '2011-05-19' 
AND t1.`class` = 'AAA'
AND t2.student_id IS NULL 

Here student_id is the primary key for both t1 and t2. If the PK is (student_id + teacher_id) then the query becomes:

INSERT INTO table2 
  (`student_id`, `studentname`, `Regno`, `class`, `date`, `session`
   , `status`, `teacher_id`) 
SELECT t1.* FROM table1 t1
LEFT JOIN table2 t2 ON (t1.student_id = t2.student_id 
                        AND t1.teacher_id = t2.teacher_id)
WHERE t1.`date` BETWEEN '2011-01-01' AND '2011-05-19' 
AND t1.`class` = 'AAA'
AND t2.student_id IS NULL  /*<<-- this stays the same provided student_id is  
                             <<-- defined as `NOT NULL` */

Here's how it works.
First we select all rows where (t1.student_id = t2.student_id); this lines up all matching rows in t1 and t2.
Because it's a left join, rows that are in t1 but NOT in t2 will have null values in the t2 columns.
By only allowing rows where t2.student_id IS NULL we only select rows from t1 that have no matching row in t2.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.