How can I duplicate a table row in mysql?

Question

This is my table farm:

+--------+
| animal |
+--------+
| cat    |
| monkey |
| bird   |
| dog    |
| horse  |
+--------+

I want to make an exact duplicate of a string animal. Therefore, my table should look like this:

+--------+---------+
| animal | animal2 |
+--------+---------+
| cat    | cat     |
| monkey | monkey  |
| bird   | bird    |
| dog    | dog     |
| horse  | horse   |
+--------+---------+

I tried

INSERT INTO `farm` (`animal2`) SELECT `animal` FROM `animals`

But what happens:

+--------+---------+
| animal | animal2 |
+--------+---------+
| cat    |         |
| monkey |         |
| bird   |         |
| dog    |         |
| horse  |         |
|        | cat     |
|        | monkey  |
|        | bird    |
|        | dog     |
|        | horse   |
+--------+---------+

Answer 1

First you must add a new column (with the same data type) and NULL:

ALTER TABLE `farm` ADD `animal2` VARCHAR(100);

Then just update:

UPDATE `farm` SET `animal2` = `animal`;

If you need to change the property table again NOT NULLwith NOT NULL.

LiveDemo

Output:

╔════════╦═════════╗
║ animal ║ animal2 ║
╠════════╬═════════╣
║ cat    ║ cat     ║
║ monkey ║ monkey  ║
║ bird   ║ bird    ║
║ dog    ║ dog     ║
║ horse  ║ horse   ║
╚════════╩═════════╝

Answer 2

To avoid deleting unrelated data in a table, use the following method.

DELETE FROM `farm` WHERE `animal1` IN (SELECT `animal` FROM `animals`);
INSERT INTO `farm` (`animal`, `animal2`) SELECT `animal`, `animal` FROM `animals`;