Results 1 to 10 of 10
  1. #1
    Join Date
    May 2007
    Posts
    139

    Unanswered: MySQL:Is any error with this query

    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...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by lse123 View Post
    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 ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2007
    Posts
    139
    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 ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2007
    Posts
    139
    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,...]?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by lse123 View Post
    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
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2007
    Posts
    139
    If I convert autoincreament to non autoincreament then the table will be sorted my the old field that was autoincreament ? now interger...

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by lse123 View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •