Q: Performing INSERT using SELECT statement with WHERE IN clause attempts to insert values not returned by SELECT statement alone I'm trying to insert values into an empty table using the following MySQL statement:
INSERT INTO buildings (name, description)
SELECT structures.name, structures.description
FROM structures
WHERE structures.structure_id IN (1, 2, 3, 4)
AND structures.deleted_at IS NULL;`

When I execute the SELECT statement alone, the expected rows are returned. However, when combined with the INSERT, I receive a "data truncation" error because the statement appears to be trying to insert a row that has been soft deleted and so has had its ID changed to something like 1__DEL__2016-06-21 18:19:53. As evidence that this is what's happening, removing 1 from the WHERE IN clause prevents the error.
The structures table looks like this:
+-----------------------------+----------+----------------------------------+----------------------+
| structure_id                | name     | description                      | deleted_at           |
+-----------------------------+----------+----------------------------------+----------------------+
| 1                           | House    | Place for people.                | null                 |
+-----------------------------+----------+----------------------------------+----------------------+
| 1__DEL__2016-06-21 18:19:53 | Home     | Another name for house.          | 2016-06-21 18:19:53  |
+-----------------------------+----------+----------------------------------+----------------------+
| 2                           | Barn     | Place for animals.               | null                 |
+-----------------------------+----------+----------------------------------+----------------------+
| 3                           | Outhouse | Place to go when you need to go. | null                 |
+-----------------------------+----------+----------------------------------+----------------------+

So, I'm curious (a) why this is happening, and (b) how I might be able to insert the data the I need without removing the soft-deleted entries from the source table.
What seemed particularly odd to me is that the WHERE IN clause seems to be match both 1 and 1__DEL__2016-06-21 18:19:53, as if it's not picking out exact matches.
Thanks.

A: Since the structure_id column in the Where clause is varchar, you should surround the values in the IN clause with quotes.
