• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

Doing an insert into two related tables at once in MYSQL

I have two tables.   One is called dictionary, the other one is called tokendictionary.  
the identity column in the dictionary corresponds to a column in the tokendictionary called dindx.   Thus, when I make an entry in the dictionary, I need to make a corresponding entry in the tokendictionary such that the identity column in the dictionary equals the foreign key, dindx in the token dictionary.    I know how to do this using TSQL in a MS SQL database.

Here is an example of the script I use:
DECLARE id int;
BEGIN TRANSACTION;
INSERT INTO [dictionary] ([code], [acute], [gender], [codetype],  [codingsystem],[papplydate],[capplydate],[type]) VALUES
('0208T','a','n','p','9','9999-01-01','2014-01-01','0');
Select @id = SCOPE_IDENTITY();
INSERT INTO [tokendictionary] ([dindx], [token]) VALUES
( @id,'AUDIOMETRY AIR ONLY');
COMMIT TRANSACTION;

Open in new window


My question is, how do I do this in MYSQL?
0
efamilant
Asked:
efamilant
  • 2
1 Solution
 
Pratima PharandeCommented:
LAST_INSERT_ID() is used in Mysql

Try to get identity like below
SELECT LAST_INSERT_ID() as fileId
0
 
Pratima PharandeCommented:
INSERT INTO table1 (title,userid) VALUES ('test', 1);
SET @last_id_in_table1 = LAST_INSERT_ID();
INSERT INTO table2 (parentid,otherid,userid) VALUES (@last_id_in_table1, 4, 1);

this is from reference site
http://stackoverflow.com/questions/3837990/last-insert-id-mysql
0
 
snoyes_jwCommented:
You don't even have to use the variable in the middle. You can call the function directly in the second INSERT statement:

INSERT INTO table2 (parentid, otherid, userid) VALUES (LAST_INSERT_ID(), 4, 1);

The function works only with AUTO_INCREMENT fields.
0
 
efamilantAuthor Commented:
This is a very nice solution to my problem.   It's so much easier than MS SQL.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now