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 · Accepted 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

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

[mysql] Insert using LEFT JOIN and INNER JOIN

Examples related to mysql

Examples related to left-join

Examples related to inner-join