Insert using LEFT JOIN and INNER JOIN

Question

Hey all i am trying to figure out how to go about inserting a new record using the following query   SELECT user id  user name  user username  user email     IF user opted in   0   NO    YES   AS optedIn   FROM    user   LEFT JOIN user permission AS userPerm ON user id   userPerm user id ORDER BY user id    My INSERT query so far is this   INSERT INTO user  SELECT    FROM user    LEFT JOIN user permission AS userPerm ON user id   userPerm user id    However  i  am not sure how to do VALUE              etc etc  when using left and inner joins   So what i am looking to do is this   User table   id      name         username      password                   OptIn -------------------------------------------------------------------- 562     Bob Barker   bBarker       BBarker priceisright com   1   And also the user permission table  user id     Permission id ------------------------- 562         4   UPDATE So like this   INSERT INTO user  name  username  password  email  opted in  VALUES   Bbarker   Bbarker   blahblahblah   Bbarker priceisright com  0   INSERT INTO user permission  user id  permission id  VALUES  LAST INSERT ID   4

User · Answer

you can t use VALUES clause when inserting data using another SELECT query  see INSERT SYNTAX  INSERT INTO user    id  name  username  email  opted in         SELECT id  name  username  email  opted in     FROM user          LEFT JOIN user permission AS userPerm             ON user id   userPerm user id

User · Answer

You have to be specific about the columns you are selecting  If your user table had four columns id  name  username  opted in you must select exactly those four columns from the query  The syntax looks like   INSERT INTO user  id  name  username  opted in    SELECT id  name  username  opted in    FROM user LEFT JOIN user permission AS userPerm ON user id   userPerm user id   However  there does not appear to be any reason to join against user permission here  since none of the columns from that table would be inserted into user   In fact  this INSERT seems bound to fail with primary key uniqueness violations   MySQL does not support inserts into multiple tables at the same time  You either need to perform two INSERT statements in your code  using the last insert id from the first query  or create an AFTER INSERT trigger on the primary table   INSERT INTO user  name  username  email  opted in  VALUES   a   b   c  0      Gets the id of the new row and inserts into the other table    INSERT INTO user permission  user id  permission id  VALUES  LAST INSERT ID    4    Or using a trigger   CREATE TRIGGER creat perms AFTER INSERT ON  user  FOR EACH ROW BEGIN   INSERT INTO user permission  user id  permission id  VALUES  NEW id  4  END

User · Answer

INSERT INTO Test  col1   col2         SELECT          a Name AS  col1           b sub AS  col2       FROM IdTable b      INNER JOIN Nametable a ON b no   a no

[mysql] Insert using LEFT JOIN and INNER JOIN

Examples related to mysql

Examples related to left-join

Examples related to inner-join