Solved

Doing an insert into two related tables at once in MYSQL

Posted on 2013-12-23
4
433 Views
Last Modified: 2013-12-26
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
Comment
Question by:efamilant
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39737489
LAST_INSERT_ID() is used in Mysql

Try to get identity like below
SELECT LAST_INSERT_ID() as fileId
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39737491
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
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 500 total points
ID: 39738082
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
 

Author Closing Comment

by:efamilant
ID: 39741108
This is a very nice solution to my problem.   It's so much easier than MS SQL.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo‚Ķ
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question