Revisions to Insert using LEFT JOIN and INNER JOIN

added 8 characters in body

Source Link

edited Apr 7, 2022 at 10:33

4.7k
7
51
82

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

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

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

deleted 29 characters in body

Source Link

edited Dec 22, 2014 at 18:34

Michael Berkowski

269.6k
47
447
392

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.

###Update after comments:

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

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.

###Update after comments:

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

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

added 489 characters in body; added 36 characters in body; added 266 characters in body; added 4 characters in body; deleted 22 characters in body

Source Link

edited Aug 22, 2012 at 13:05

Michael Berkowski

269.6k
47
447
392

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.

###Update after comments:

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

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.

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.

###Update after comments:

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

added 22 characters in body

Source Link

edited Aug 22, 2012 at 12:55

Michael Berkowski

269.6k
47
447
392

Loading

Source Link

created Aug 22, 2012 at 12:50

Michael Berkowski

269.6k
47
447
392

Loading

Collectives™ on Stack Overflow

Return to Answer