Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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  
  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);
                Why are you inserting into user when you have just selected values from user?  What new row are you intending to insert?
– Michael Berkowski
                Aug 22, 2012 at 12:53
                @AaronJSpetner  when you propose an edit, please make it a complete edit so that subsequent edits are not required to fix casing, punctuation, and removal of useless text like "hey all".
– mickmackusa
                Jul 8, 2018 at 10:39
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)
                I am in need of inserting a row in the user_permission table as well along side the user table. How would i do that?
– StealthRT
                Aug 22, 2012 at 12:58
                @StealthRT You can't insert new rows into 2 tables without multiple statements, or a trigger on the first table.
– Michael Berkowski
                Aug 22, 2012 at 13:07
                I updated my OP again to reflect what i am thinking you mean by your post. Let me know if its correct. Would i need to run "update" between the 2 inserts?
– StealthRT
                Aug 22, 2012 at 13:11
                @StealthRT Yes, just like that - but you must execute the second immediately after the first, or the value of LAST_INSERT_ID() will become invalid.  The LAST_INSERT_ID() is connection dependent, so as long as these are executed sequentially on the same connection, you'll get the right value back. If any other inserts happen in between, you'll get the wrong value.
– Michael Berkowski
                Aug 22, 2012 at 13:18
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.