CODEWITHSUNDEEP
mysqlsql
BurningBagel
BurningBagel

Reputation: 5

SQL Error Code 1366 Incorrect integer value at row 88163

I'm trying to do the following query 

INSERT INTO 'table_1'(column_1)  
    SELECT DISTINCT 'column 2'  
    FROM other_schema.table2;

Both of those columns have their fields set to INT(255), and yet I get the following error:

"Error Code: 1366. Incorrect integer value: 'column2' for column 'column2' at row 88163"

I even went and checked the individual row, but the field is a number(5800 to be precise).

I did some tinkering, and it seems that if I try to limit the select, it always accuses the row immediately after the upper limit of being of an incorrect value.

Upvotes: 0

Views: 10070

Answers (3)

phobia82
phobia82

Reputation: 1257

If you use single quotes around 'column 2' sql will interpret that as a string, you need to remove the quote, or add backticks ` if the table name has spaces

SELECT DISTINCT `column 2`

Upvotes: 1

gr1zzly be4r
gr1zzly be4r

Reputation: 2152

The way you've written your query currently is that you're trying to INSERT a string into your integer column. When you do

SELECT DISTINCT 'column2'

you are selecting the literal string 'column2' and not the values from that column. I would unquote the references to your table and column:

INSERT INTO table SELECT DISTINCT column2 FROM ...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269593

It seems pretty clear to me that table2(column2) is not a number. You may think it is for some reason, but it is not.

However, table1(column1) is a number. So, one method to solve this is to use silent conversion. That is, convert the value to a number but in such a way that no error occurs. MySQL does this with + 0:

INSERT INTO table1(column1)  
    SELECT DISTINCT (column2 + 0) 
    FROM other_schema.table2;

I would suggest adding where (column2 + 0) <> 0, assuming that you never expect 0 for this column.

I should also note that you have no idea what row is 88,163 for the insert. This takes place after the distinct, so it is very unlikely that it is the row identified as 88,168th in the table.

Upvotes: 0

Related Questions