0

How can I insert the data into multiple tables using a select query?

INSERT INTO PermissionRole(code,isValid,longValue,shortValue),
INSERT INTO PermissionRole_authorisedFunctions 
VALUES(LAST_INSERT_ID(),brfm.authorisedFunctionCode)
SELECT urt.code, urt.isValid, urt.longValue, urt.shortValue, brfm.authorisedFunctionCode 
FROM Ref_UserRoleType urt 
INNER JOIN ref_businessrolefunctionmap brfm
ON urt.code=brfm.businessRoleCode;
Renz Dominique
  • 168
  • 13
Basil
  • 1
  • 3
    Possible duplicate of [INSERT rows into multiple tables in a single query, selecting from an involved table](https://stackoverflow.com/questions/10471757/insert-rows-into-multiple-tables-in-a-single-query-selecting-from-an-involved-t) – Laurenz Albe Dec 11 '18 at 04:37
  • Since i don't have enough reputation to comment, i'm giving this in answer's section. This question already have an answer in stackoverlfow. check the answers in https://stackoverflow.com/questions/10471757/insert-rows-into-multiple-tables-in-a-single-query-selecting-from-an-involved-t – G Prakash Dec 11 '18 at 04:31
  • If you are not on mysql 8 or above the use of cte as explained in the links mentioned in the previous 2 comments (which was a potgresql question BTW) is not available to you. An insert can only insert to 1 table it may be worth your while looking at a trigger. – P.Salmon Dec 11 '18 at 09:06
  • Does this answer your question? [Mysql - Insert values into multiple tables with unknown primary key](https://stackoverflow.com/questions/5486670/mysql-insert-values-into-multiple-tables-with-unknown-primary-key) – outis Jul 22 '22 at 18:38

0 Answers0