使用LEFT JOIN和INNER JOIN插入 [英] Insert using LEFT JOIN and INNER JOIN
问题描述
嘿,我正在尝试找出如何使用以下查询插入新记录的方法:
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;
到目前为止,我的INSERT
查询是:
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.
所以我要做的是:
User
表:
id | name | username | password | OptIn
--------------------------------------------------------------------
562 Bob Barker bBarker BBarker@priceisright.com 1
还有user_permission
表
user_id | Permission_id
-------------------------
562 4
更新 像这样吗?
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
表具有四列id, name, username, opted_in
,则必须从查询中准确选择这四列.语法如下:
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
但是,这里似乎没有任何理由要加入user_permission
,因为该表中的任何列都不会插入到user
中.实际上,此INSERT
似乎因主键唯一性冲突而失败.
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不支持同时插入多个表.您需要使用第一个查询的最后一个插入ID在代码中执行两个INSERT
语句,或者在主表上创建一个AFTER INSERT
触发器.
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)
或使用触发:
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
这篇关于使用LEFT JOIN和INNER JOIN插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!