CODEWITHSUNDEEP
phpmysql
KLH
KLH

Reputation: 3

Insert into select - not working

I've a problem with my application, The records are not being copied from one table say tableA to another table say tableB using PHP & MYSQL. The problem appears only if the primary key is 19 digit long and starting with digit 9 and works fine for all other primary keys. There is no error reported, the process simply fails to copy the results.

I use the following query to copy the records -

INSERT into tableB (select * from tableA where primary_key_id=id)

Queries are working fine when executed manually using Toad / phpmyadmin.

Any ideas or suggestions to resolve this would be greatly appreciated.

Upvotes: 0

Views: 666

Answers (4)

mvp
mvp

Reputation: 116068

For INSERT ... SELECT to work, you either must provide list of columns for INSERT, or have SELECT output match exactly tableB definition.

If tableB definition is exactly the same as tableA, your query should work (using INSERT, not INSET). You can also create tableB at once using this:

CREATE TABLE tableB AS SELECT * FROM tableA ...

If your tableA and tableB are indeed the same structure, one of the reasons why your INSERT with 19-digit long primary key may fail is because you might be trying to insert duplicate keys from tableA into tableB: your INSERT copies everything, including primary key value.

UPDATE: According to MySQL documentation, maximum BIGINT value is 9,223,372,036,854,775,807 - 19 digits, max value starts with 9. This sounds suspiciously close to your description of the problem. I believe that you are simply running out of maximum allowable value for BIGINT, and fact that you want BIGINT(20) is not going to help you.

Upvotes: 1

nvanesch
nvanesch

Reputation: 2600

Ok i have exactly located the problem.. it is an integer overflow in php.

When PHP encounters a number that is getting too large to store in a long, it will put it in a float. and when it echoes that float it will use the float notation.

And coincidentally it is a 19 digit number starting with a 9 where it starts to go wrong.

$large_number = 9223372036854775807;
var_dump($large_number);                     // int(9223372036854775807)

$large_number = 9223372036854775808;
var_dump($large_number);                     // float(9.2233720368548E+18)

So you should keep the primary key as a string or use a number formatter to output it.

source: http://php.net/manual/en/language.types.integer.php

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

do you mean 

 INSERT

and not 

INSET

here how you can do it

insert into table_name (column1, column2) values (select column3, column4 from table2 where ...)

Upvotes: 0

Vijaya Pandey
Vijaya Pandey

Reputation: 4282

Try in the following way:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2

Upvotes: 0

Related Questions