MySQL:Is any error with this query

lse123 · #1 (**permalink**) 11-06-09, 18:33

MySQL

Code:

$query1 ="INSERT INTO $CanceledCustomersTable (SELECT * FROM $CustomersTable WHERE status='deleted' AND email='$email2' AND password='$password2');";

Is any error with this query if $CanceledCustomersTable & $CustomersTable are identical of exactly BUT first field is AUTOINCREMENT in $CustomersTable something non-true in $CanceledCustomersTable ...? Always gives false(=>$result1 = @mysql_query($query1,$linkid)

...

to use in a MySQL Transaction...

r937 · #2 (**permalink**) 11-06-09, 19:35

Quote:

Originally Posted by lse123

Is any error with this query

yes, is any error

first, remove the parentheses around the SELECT

second, replace the dreaded, evil "select star" (SELECT * ) with a list of all the columns except the auto_increment

then add the list of all columns except the auto_increment in parentheses in front of the SELECT

INSERT INTO foo ( col2, col3, col4 )
SELECT col2, col3, col4 FROM foo WHERE ...

lse123 · #3 (**permalink**) 11-07-09, 05:33

Q1: This way autoincreament field it will be included ? I want to be included...
Please note $CanceledCustomersTable & $CustomersTable are identical of exactly BUT first field is AUTOINCREMENT in $CustomersTable something non-true in $CanceledCustomersTable ...

Q2: If I insert in a table two identical records(no autoincreament) it will give error and do not insert second correct ?

r937 · #4 (**permalink**) 11-07-09, 06:15

you need to start testing

create two identical tables, add some rows, and give it a try

it will be easier for you than constantly coming back here and asking

lse123 · #5 (**permalink**) 11-09-09, 06:03

I guess $CanceledCustomersTable can NOT have an AUTOINCREAMENT FIELD?
in an AUTOINCREAMENT FIELD of a new table, how I may start count from 10000 [10001, 10002,...]?

healdem · #6 (**permalink**) 11-09-09, 06:12

why are you trying to achieve this?

autonumber fields have no meaning outside the system they were created in. there are good arguemnt never to show the autogenerated number to a user, precisely beaucse they then try to assign another meaning to the autonumber column.

if you need a number to have significance outside the system then explcitly create such a number from a suitable method. that may mean locking the table to grab the next value, then releaseing the locks.

r937 · #7 (**permalink**) 11-09-09, 06:15

Quote:

Originally Posted by lse123

in an AUTOINCREAMENT FIELD of a new table, how I may start count from 10000 [10001, 10002,...]?

you simply gots to start testing some of this stuff yourself...

Code:

CREATE TABLE lse123
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, foo VARCHAR(99)
) AUTO_INCREMENT = 10000
;
INSERT INTO lse123 (foo) 
VALUES ( 'nine' ) , ( 'three' ) , ( 'seven' ) 
;
SELECT * FROM lse123
;

lse123 · #8 (**permalink**) 11-22-09, 09:43

If I convert autoincreament to non autoincreament then the table will be sorted my the old field that was autoincreament ? now interger...

r937 · #9 (**permalink**) 11-22-09, 09:53

Quote:

Originally Posted by lse123

If I convert autoincreament to non autoincreament then the table will be sorted my the old field that was autoincreament ? now interger...

tables are not sorted in any way

only query results are sorted

if you want to see data in a particular sequence, use SELECT with ORDER BY

dav1mo · #10 (**permalink**) 11-24-09, 17:42

I think what the OP is after is to actually carry the nbr from the one table into the other. Meaning in his active table he has auto-increment on a column and in second table he has same name column, but it doesn't auto-increment. That is fine, then you just go back to Rudy's original query and add that column into the insert and select portion.
Dave

Internet Services

Web Development

Digital Marketing

Consumer Tech