insert data to specific ID

tommyy

hi all, does anybody can tell me how to insert data to specific id which mean that i want to store record to mysql which is not follow the last link identifier "ID"

Currently my database had 10 record, if i insert next data to mysql which the key id will increment to 11, but i want store the record to key id 20, leave the 11-19 blank.
Is it possible ?

The other question is how to override the record and how to prompt alert before override.

NogDog

If we're talking about an auto-incrementing integer that is the primary key, there is nothing stopping you from specifying a new (so far unused) value for it. Perhaps the real question would be, "why?" Normally such a field's only purpose is to provide a unique identifier for that row, not to have any "meaning" such that it matters what the number is. If it does matter for you, it may be that you either need a separate field for that purpose, or else it should not be an auto-increment field in the first place.

tommyy

yup...you are right absolutely

i will create other field to do that.

how about update/override specific record

for example the table contain :

               Code           Color      Label

ID 10 AC1010 Red 1
ID 11 AC1011 Green 2
ID 12 AC1012 Orange 3
ID 13 AC1013 Green 5

In case i want to update/override the record in ID 12
what is the code look like

bradgrafelman

Depends on your specific needs. First thought that comes to my mind is to use the INSERT ... ON DUPLICATE KEY UPDATE Syntax.

EDIT:

tommyy wrote:
i will create other field to do that.

If this "ID" value you are getting is a unique identifier (determined by your specific application/data/source/etc.), then there's no need to create a separate column. You can still use this ID column as the PRIMARY key for your table... it just sounds like it shouldn't be an AUTO_INCREMENT column as well (since the ID value isn't arbitrary and is tied to a specific set of data, thus an automatically generated ID value wouldn't be appropriate).

tommyy

can i modify the record base on searching the code column

i mean that the statement where ....code = ??

is it possible

tommyy

what is wrong in this statement

mysql_query("INSERT INTO example (code, color, label) WHERE code = AC1010 values (AC2222, white, 60) ON DUPLICATE KEY UPDATE");

NogDog

INSERTs do not have a WHERE clause. Also, the literal string values must be quoted in the query.

$query = "INSERT INTO example (code, color, label) values ('AC2222', 'white', 60) ON DUPLICATE KEY UPDATE";

tommyy

how does the system know which record should be update/override if there is no where clause ?

how can i update/override the record in code AC1012 for below instance ?

ID Code Color Label
ID10 AC1010 Red 1
ID11 AC1011 Green 2
ID12 AC1012 Orange 3
ID13 AC1013 Green 5

thanks NogDog

bradgrafelman

tommyy wrote:
how does the system know which record should be update/override if there is no where clause ?

It knows which record needs to be updated because the ID column should have a UNIQUE or PRIMARY key. Thus, when you attempt to INSERT a row with the same ID value, the unique constraint will fail causing the INSERT statement to fail because of the existing row. The "ON DUPLICATE KEY UDPATE ..." clause is then used to update that existing row with different data.

Read the MySQL manual page I linked to above - it explains all this and more (and even includes example queries).

Tonata

tommyy;10955582 wrote:
how does the system know which record should be update/override if there is no where clause ?

how can i update/override the record in code AC1012 for below instance ?

ID Code Color Label
ID10 AC1010 Red 1
ID11 AC1011 Green 2
ID12 AC1012 Orange 3
ID13 AC1013 Green 5

thanks NogDog

What you want is to UPDATE not INSERT, so you don't use INSERT.

visit: http://www.w3schools.com/sql/sql_update.asp

bradgrafelman

Tonata;10955716 wrote:
What you want is to UPDATE not INSERT, so you don't use INSERT.

No, what he wants to do is an INSERT (specifically, with the "ON DUPLICATE KEY UPDATE" clause added onto the end). Read the entire thread next time. 😉