#Mysql insert sql table update#
Optionaly you can use UPDATE similar to step III, before this step, to find whom users are already inside (and mark them in tmp table). Here, "IGNORE" can be used, if you allow some users already to be inside. INSERT IGNORE INTO users (username, password) Here, instead of $ALL_VAL you place list of values: ('test1','test1','bio1','home1').,('testn','testn','bion','homen') INSERT INTO tmp (username, password, bio, homepage) VALUES $ALL_VAL CREATE TEMPORARY TABLE tmp (id bigint(20) NOT NULL. Step I: Create temporary table to store new data. I find the following solution (just 5 queryes): What would happen, if you want to create many such records ones (to register 10 users, not just one)? Insert into user_profile (user_id, homepage) values (v_user_id, p_homepage) Set v_user_id = last_insert_id() - save the newly created user_id Insert into users (username) values (p_username) Profile_id int unsigned not null auto_increment primary key,ĭrop procedure if exists insert_user_and_profile ĭeclare v_user_id int unsigned default 0 User_id int unsigned not null auto_increment primary key, That's why I used the BEGIN and COMMIT here.įairly simple if you use stored procedures: call insert_user_and_profile('f00','') If however, you decide "either all queries finish, or none finish - I do not want rows in some tables but no matching rows in others, I always want my database tables to be consistent", you need to wrap all statements in a transaction. If you can live with "some have finished, others not", don't read on.
Whatever way of solving this you choose, you must decide what should happen should the execution be interrupted between queries (for example, your database-server crashes). Use your language to retrieve the LAST_INSERT_ID(), either by executing that literal statement in MySQL, or using for example php's mysql_insert_id() which does that for you.SELECT LAST_INSERT_ID() INTO INTO table2. If you still need that of table 1 afterwards, we will have to store it in a variable. Unfortunately, when the second statement itself inserts rows in a table with an auto-increment column, the LAST_INSERT_ID() will be updated to that of table 2, and not table 1. Let me elaborate: there are 3 possible ways here: Can't I simply put the just generated ID in a $var and put that $var in all the MySQL commands?" You said " After all this time trying to figure it out, it still doesn't work. Have a look at LAST_INSERT_ID() to reuse autoincrement values.
VALUES(LAST_INSERT_ID(),'Hello world!', '') INSERT INTO profiles (userid, bio, homepage) No, you can't insert into multiple tables in one MySQL command.
0 kommentar(er)
