CODEWITHSUNDEEP
mysqlinsertinner-joinmysql-workbench
EB.
EB.

Reputation: 2757

Insert an auto incremented ID from Table 2 into Table 1 on insert statement MySql

I have a stored procedure in MySql, where I an trying to insert values into Person_Table. One of the columns in Person_Table is CompanyID.

But I need to retrieve that CompanyID from Company_Table.CompanyID is an auto incremented Primary key. Both tables also have columns CompanyName. I want to insert Company+Table.CompanyID into the Person_Table.CompanyID column in the same procedure.

I am having problems inner joining the 2 tables. Can you inner join tables on an insert statement?

This is what I have so far but it doesn't like the inner join:

        insert into person p
    (FarmName, FirstName, MiddleName, LastName, Phone, CompanyID)
    values
   ( oFarmName, oFirstName, oMiddleName, oLastName, oPhone, oCompanyID)   

    inner join company c on p.FarmNamne =  c.CompanyName
    where p.FarmName = c.CompanyID;

Upvotes: 0

Views: 974

Answers (2)

Aust
Aust

Reputation: 11622

If I understand your question properly, then this should work after renaming the tables and columns to match your information:

INSERT INTO Person_Table
(FarmName, FirstName, MiddleName, LastName, Phone, CompanyID)
VALUES
( oFarmName, oFirstName, oMiddleName, oLastName, oPhone, (
    SELECT c.CompanyId FROM Person_Table p 
    INNER JOIN Company_Table c ON c.CompanyName = p.CompanyName)
)

This will only work if there are no duplicate Company Names but give it a shot.

Upvotes: 0

Horen
Horen

Reputation: 11382

I cannot answer whether it is possible to insert on an inner join but my feeling says it's not possible.

Anyways, to offer a little help, I would suggest that you solve this problem differently:

How about you put a trigger on your first table AFTER INSERT. At this point of time the auto increment id is known and can easily be inserted in table 2 with your trigger.

http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html

Upvotes: 0

Related Questions