copy tableA to tableB if column in A is not duplicate

Question

I am trying to copy rows from tableA to a blank tableB.

tableA has a column mytext and the rows have duplicates from this column.

question How can i copy from A to B where there are no duplicate rows from mytext? In other words if 2 or more rows have identical values in mytext keep the one with lowest id number and copy to tableB.

originally i tried to delete the duplicates but kept getting a an error Error Code: 1205. Lock wait timeout exceeded from this sql command:

DELETE n1 
FROM tableA n1, tableA n2 
WHERE n1.id > n2.id 
AND n1.mytext = n2.mytext

I am on amazons RDS

+---+-----+-----+------+-------+
|id |fname|lname|mytext|morevar|
|---|-----|-----|------|-------|
|   |     |     |      |       |
|   |     |     |      |       |
|   |     |     |      |       |
|   |     |     |      |       |
+------------------------------+ 
CREATE TABLE `tableB` (
    `id` int(11) NOT NULL,
    `fname` varchar(45) DEFAULT NULL,
    `lname` varchar(45) DEFAULT NULL,
    `mytext` mediumtext,
    `morevar` bigint(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

this did not work, it did not catch the duplicates and simply copied all the values from tableA over to tableB

INSERT INTO tableB (`id`,`fname`,`lname`,`mytext`,`morevar`)
SELECT DISTINCT tableA.id,tableA.fname,tableA.lname,tableA.mytext,tableA.morevar
FROM tableA

Answer 1

try this

INSERT INTO tableB (`id`,`fname`,`lname`,`mytext`,`morevar`)
SELECT id, fname, lname, mytext, morevar
FROM tableA a
WHERE id = (Select Min(id) FROM tableA
            WHERE myText = a.mytext)

if you need to do this in batches... (edit myText criteria to control batches)

INSERT INTO tableB (`id`,`fname`,`lname`,`mytext`,`morevar`)
SELECT id, fname, lname, mytext, morevar
FROM tableA a
WHERE mytext < 'j'
   And id = (Select Min(id) FROM tableA
            WHERE myText = a.mytext)

INSERT INTO tableB (`id`,`fname`,`lname`,`mytext`,`morevar`)
SELECT id, fname, lname, mytext, morevar
FROM tableA a
WHERE mytext >= 'j'
   And mytext < 'r'
   And id = (Select Min(id) FROM tableA
            WHERE myText = a.mytext)

INSERT INTO tableB (`id`,`fname`,`lname`,`mytext`,`morevar`)
SELECT id, fname, lname, mytext, morevar
FROM tableA a
WHERE mytext >= 'r'
   And id = (Select Min(id) FROM tableA
            WHERE myText = a.mytext)