Insert using LEFT JOIN and INNER JOIN
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
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
)
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
);