INSERT with WHERE condition

[boneXXX]

Hi all,

 

I need your advise for inserting data into database.

 

To understand the concept could you just help me on this made up example please :

 

such as there are ID(Primary), firstname, surname columns and I want to insert fistname data first then surname data second in different points.

 

INSERT INTO Member(firstname) VALUES ('Max');

 

INSERT INTO Member(surname) VALUES('Power');

 

The data will be inserted in two different rows. So my question is how can I use the automatically generated ID number to insert this values into the same row in two different points. I don't does make sense or not, it is just a example that I made up. Could you help me on this please? Thank you.

[boneXXX]

Yeah I don't need to use INSERT I just need to UPDATE the row. But how can I tell which row to UPDATE the ID number  without telling to the program manually?

[boneXXX]

I think I need to SELECT the ID number after INSERTing the "firstname" and use that ID number to UPDATE the row when I want to insert the "surname".

 

Am I right?

[revraz]

Pretty much.  You can get the ID as the row is created as well.  How long after you create the Firstname are you looking to set the Surname?  Same script or different?  If you wait too long and you get more than 1 firstname that is the same, you're looking at some problems you'll have to figure out.

[Barand]

Either

INSERT INTO member (firstname, surname) VALUES ('Max', 'Power')

 

or

INSERT INTO Member(firstname) VALUES ('Max');

UPDATE Member SET surname='Power' WHERE id = LAST_INSERT_ID();

 

Note that LAST_INSERT_ID is only available until the current mysql connection is closed