Find duplicates in one table and move them to another.

[nishika]

2 tables are available.

Table 1.

num                         value
порядковый номер строки     значение

Table 2.

num1                         value       num2
порядковый номер строки 1    значение    порядковый номер строки 2

How do SQL request to find all the duplicate lines in the first table and put them in the second (in the second) num1 row number one, value The second column from the first table is moved to num2 The row number, where this value is duplicated, is included.

Example:

1   a
2   b
3   c
4   a
5   c
num1    value   num2

1       a       4

3       c       5

Tables are in the database SQL

[inna]

In order to select the data, I can offer such a request:

select t1.num, t1.value, t2.num
from Test t1
join Test t2 on t1.value = t2.value and t1.num < t2.num

http://sqlfiddle.com/#!9/78c97/1

Well, I'll get the data back. http://www.w3schools.com/sql/sql_insert_into_select.asp