Professional Web Applications Themes

INSERT and AUTO_INCREMENT - MySQL

If I have a table with a number of columns in, the first of which is an auto-increment field for indexing, is there a way of inserting data without having to specify all the column names that aren't the auto increment field? INSERT INTO tables VALUES(....) Or alternatively could I insert a value into the autoincrement field above without messing up the auto-increment? Cheers, Ben...

  1. #1

    Default INSERT and AUTO_INCREMENT

    If I have a table with a number of columns in, the first of which is an
    auto-increment field for indexing, is there a way of inserting data
    without having to specify all the column names that aren't the auto
    increment field?

    INSERT INTO tables VALUES(....)

    Or alternatively could I insert a value into the autoincrement field
    above without messing up the auto-increment?

    Cheers,

    Ben

    Ben Guest

  2. #2

    Default Re: INSERT and AUTO_INCREMENT

    Ben wrote: 

    use NULL as a placeholder for the auto-increment field.


    Paul Guest

  3. #3

    Default Re: INSERT and AUTO_INCREMENT

    Ben wrote: 

    Ben,

    As Paul indicated, you can use NULL for the value.

    But IMHO it's always better to specify the column names on any such
    request. That way you don't have as much code to change should you need
    to go back and add a (not-null) column later.

    But yes, I agree, it's more typing and a PITA. But I've gotten "bit" by
    this before and had to find a LOT of code to change!

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  4. #4

    Default Re: INSERT and AUTO_INCREMENT

    You can specify the column names like so:
    INSERT INTO ('col1','col2','col3') VALUES ('1','2','3');
    etc

    Hope that helps

    aeg Guest

  5. #5

    Default Re: INSERT and AUTO_INCREMENT

    On Fri, 29 Dec 2006 17:37:55 -0500, Jerry Stuckle wrote: 

    As an example of how it can bite one, imagine forgetting to update one
    sweeping but rarely used query as part of inserting a new column into
    the table any place other than the last one in the table.

    --
    4. Shooting is not too good for my enemies.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  6. #6

    Default Re: INSERT and AUTO_INCREMENT

    aeg wrote: 

    But isn't this exactly what the OP said he didn't want to do: " is there a
    way of inserting data
    WITHOUT having to specify all the column names that aren't the auto
    increment field?"


    Paul Guest

  7. #7

    Default Re: INSERT and AUTO_INCREMENT

    Peter H. Coffin wrote: 
    >
    >
    > As an example of how it can bite one, imagine forgetting to update one
    > sweeping but rarely used query as part of inserting a new column into
    > the table any place other than the last one in the table.
    >[/ref]

    Yep, and even it it's the last one it will bite you. You'll get a
    mismatch on the number of columns vs. the number of data items.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  8. #8

    Default Re: INSERT and AUTO_INCREMENT

    On Sat, 30 Dec 2006 19:51:05 -0000, Paul Lautman wrote: 
    >
    > But isn't this exactly what the OP said he didn't want to do: " is there a
    > way of inserting data
    > WITHOUT having to specify all the column names that aren't the auto
    > increment field?"[/ref]

    In which case the answer is simple "No. The right way is to specify the
    column names."

    Normally that only burdensome the first time; after that, you've got the
    query saved someplace, and you merely update it occasionally.

    --
    "... I've seen Sun monitors on fire off the side of the multimedia lab.
    I've seen NTU lights glitter in the dark near the Mail Gate.
    All these things will be lost in time, like the root partition last week.
    Time to die...". -- Peter Gutmann in the scary.devil.monastery
    Peter Guest

Similar Threads

  1. Replies: 4
    Last Post: December 20th, 04:02 PM
  2. Replies: 2
    Last Post: November 25th, 03:04 PM
  3. Replies: 4
    Last Post: October 11th, 11:30 PM
  4. How to personalise an auto_increment reference field ?
    By Baptiste Pillot in forum MySQL
    Replies: 11
    Last Post: June 16th, 03:31 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139